欢迎使用优快云-markdown编辑器

最新推荐文章于 2021-01-07 23:01:28 发布
最新推荐文章于 2021-01-07 23:01:28 发布
阅读量529 收藏
点赞数 1
CC 4.0 BY-SA版权
文章标签： phalcon
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/silvercell/article/details/54278938

Phalcon 查询语言（Phalcon Query Language (PHQL)）

Phalcon查询语言，PhalconQL或者简单的称之为PHQL，是一种面向对象的高级SQL语言，允许使用标准化的SQL编写操作语句。 PHQL实现了一个解析器（C编写）来把操作语句解析成RDBMS的语法。

为了达到高性能，Phalcon实现了一个和 SQLite 中相似的解析器。它只占用了非常低的内存，同时也是线程安全的。

The parser first checks the syntax of the pass PHQL statement, then builds an intermediate representation of the statement and finally it converts it to the respective SQL dialect of the target RDBMS.

In PHQL, we’ve implemented a set of features to make your access to databases more secure:

  • Bound parameters are part of the PHQL language helping you to secure your code

  • PHQL only allows one SQL statement to be executed per call preventing injections

  • PHQL ignores all SQL comments which are often used in SQL injections

  • PHQL only allows data manipulation statements, avoiding altering or dropping tables/databases by mistake or externally without authorization

  • PHQL implements a high-level abstraction allowing you to handle tables as models and fields as class attributes

    ​

    解析器首先检查通过PHQL语句的语法,然后构建一个语句的中间过程,最后将其转换为相应的SQL方言的目标RDBMS。

    在PHQL,我们实现一组特性使你对数据库的访问更安全:

    ​ 绑定参数是PHQL语言的一部分帮助你确保你的代码安全性

    ​ PHQL只允许一个SQL语句执行来防止sql 注入

    ​ PHQL忽略所有SQL注释,因为这些注释常被恶意用于SQL注入

    ​ PHQL只允许数据操作语句,避免误修改或删除表/数据库或外部没有授权

    ​ PHQL实现高级抽象允许您处理表类似处理模型，处理字段类似于类的属性

范例（Usage Example）

为了更好的说明PHQL是如何使用的，在接下的例子中，我们定义了两个模型 “Cars” and “Brands”:

<?php

use Phalcon\Mvc\Model;

class Cars extends Model
{
    public $id;

    public $name;

    public $brand_id;

    public $price;

    public $year;

    public $style;

    /**
     * This model is mapped to the table sample_cars
     */
    public function getSource()
    {
        return "sample_cars";
    }

    /**
     * A car only has a Brand, but a Brand have many Cars
     */
    public function initialize()
    {
        $this->belongsTo("brand_id", "Brands", "id");
    }
}

然后每辆车(Car)都有一个品牌(Brand)，一个品牌(Brand)却可以有很多辆车(Car):

<?php

use Phalcon\Mvc\Model;

class Brands extends Model
{
    public $id;

    public $name;

    /**
     * The model Brands is mapped to the "sample_brands" table
     */
    public function getSource()
    {
        return "sample_brands";
    }

    /**
     * A Brand can have many Cars
     */
    public function initialize()
    {
        $this->hasMany("id", "Cars", "brand_id");
    }
}

创建 PHQL 查询（Creating PHQL Queries）

PHQL查询可以通过实例化 Phalcon\Mvc\Model\Query 这个类来创建:

<?php

use Phalcon\Mvc\Model\Query;

// Instantiate the Query
$query = new Query(
    "SELECT * FROM Cars",
    $this->getDI()
);

// Execute the query returning a result if any
$cars = $query->execute();

Phalcon\Mvc\Model\Query  public getDI () Returns the dependency injection container

在控制器或者视图中，通过 models manager 可以非常容易 create/execute PHQL查询:

<?php

// Executing a simple query
$query = $this->modelsManager->createQuery("SELECT * FROM Cars");
$cars  = $query->execute();

// With bound parameters
$query = $this->modelsManager->createQuery("SELECT * FROM Cars WHERE name = :name:");
$cars  = $query->execute(
    [
        "name" => "Audi",
    ]
);

或者使用一种更简单的执行方式:

<?php

// Executing a simple query
$cars = $this->modelsManager->executeQuery(
    "SELECT * FROM Cars"
);

// Executing with bound parameters
$cars = $this->modelsManager->executeQuery(
    "SELECT * FROM Cars WHERE name = :name:",
    [
        "name" => "Audi",
    ]
);

选取记录（Selecting Records）

和SQL类似，PHQL也允许使用SELECT来查询记录，但必须用模型类替换语句的表名:

<?php

$query = $manager->createQuery(
    "SELECT * FROM Cars ORDER BY Cars.name"
);

$query = $manager->createQuery(
    "SELECT Cars.name FROM Cars ORDER BY Cars.name"
);

带命名空间的模型类名也是允许的:

<?php

$phql  = "SELECT * FROM Formula\Cars ORDER BY Formula\Cars.name";
$query = $manager->createQuery($phql);

$phql  = "SELECT Formula\Cars.name FROM Formula\Cars ORDER BY Formula\Cars.name";
$query = $manager->createQuery($phql);

$phql  = "SELECT c.name FROM Formula\Cars c ORDER BY c.name";
$query = $manager->createQuery($phql);

PHQL支持绝大多数的标准SQL语法，甚至非标准的SQL语法也支持，比如LIMIT:

<?php

$phql = "SELECT c.name FROM Cars AS c WHERE c.brand_id = 21 ORDER BY c.name LIMIT 100";

$query = $manager->createQuery($phql);

结果类型（Result Types）

查询结果的类型依赖于我们查询时列的类型，所以结果类型是多样化的。 如果你获得了一个完整的对象，那么这个对象是 Phalcon\Mvc\Model\Resultset\Simple 的实例。 这样的查询结果集是一组完整的模型对象:

<?php

$phql = "SELECT c.* FROM Cars AS c ORDER BY c.name";

$cars = $manager->executeQuery($phql);

foreach ($cars as $car) {
    echo "Name: ", $car->name, "\n";
}

下面这种方式的查询结果集也是一样的:

<?php

$cars = Cars::find(
    [
        "order" => "name"
    ]
);

foreach ($cars as $car) {
    echo "Name: ", $car->name, "\n";
}

完整的对象中的数据可以被修改，并且可以重新保存在数据库中，因为它们在数据表里面本身就是一条完整的数据记录。 但是如下这种查询方式，就不会返回一个完整的对象:

<?php

$phql = "SELECT c.id, c.name FROM Cars AS c ORDER BY c.name";

$cars = $manager->executeQuery($phql);

foreach ($cars as $car) {
    echo "Name: ", $car->name, "\n";
}

我们只想要数据表中的一些字段，尽管返回的结果集对象仍然是 Phalcon\Mvc\Model\Resultset\Simple 的实例，但是却不能认为是一个完整的对象。 上述例子中，返回的结果集中的每个对象仅仅只有两个列对应的数据。

These values that don’t represent complete objects are what we call scalars. PHQL allows you to query all types of scalars: fields, functions, literals, expressions, etc..:

这些值不代表完整的对象是我们所说的标量。PHQL允许您查询所有的标量类型:字段、功能,文字,表情,等:

<?php

$phql = "SELECT CONCAT(c.id, ' ', c.name) AS id_name FROM Cars AS c ORDER BY c.name";

$cars = $manager->executeQuery($phql);

foreach ($cars as $car) {
    echo $car->id_name, "\n";
}

As we can query complete objects or scalars, we can also query both at once:

我们可以查询完整的对象或标量,同时我们也可以查询两者:

<?php

$phql = "SELECT c.price*0.16 AS taxes, c.* FROM Cars AS c ORDER BY c.name";

$result = $manager->executeQuery($phql);

The result in this case is an object Phalcon\Mvc\Model\Resultset\Complex. This allows access to both complete objects and scalars at once:

结果在这种情况下是一个对象Phalcon \ Mvc \ \ Resultset \复杂模型。这允许同时访问完整的对象和标量:

<?php

foreach ($result as $row) {
    echo "Name: ", $row->cars->name, "\n";
    echo "Price: ", $row->cars->price, "\n";
    echo "Taxes: ", $row->taxes, "\n";
}

Scalars are mapped as properties of each “row”, while complete objects are mapped as properties with the name of its related model.

标量映射作为属性的“行”,而完整的对象映射作为属性名称的相关模型

连接（Joins）

通过PHQL可以非常方便的从多个模型中请求数据记录。PHQL支持绝大多数的JOIN操作。

As we defined relationships in the models, PHQL adds these conditions automatically:

我们定义的关系模型,PHQL自动添加这些条件:

<?php

$phql = "SELECT Cars.name AS car_name, Brands.name AS brand_name FROM Cars JOIN Brands";

$rows = $manager->executeQuery($phql);

foreach ($rows as $row) {
    echo $row->car_name, "\n";
    echo $row->brand_name, "\n";
}

By default, an INNER JOIN is assumed. You can specify the type of JOIN in the query:

默认情况下,内连接。您可以指定查询中加入的类型:

<?php

$phql = "SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands";
$rows = $manager->executeQuery($phql);

$phql = "SELECT Cars.*, Brands.* FROM Cars LEFT JOIN Brands";
$rows = $manager->executeQuery($phql);

$phql = "SELECT Cars.*, Brands.* FROM Cars LEFT OUTER JOIN Brands";
$rows = $manager->executeQuery($phql);

$phql = "SELECT Cars.*, Brands.* FROM Cars CROSS JOIN Brands";
$rows = $manager->executeQuery($phql);

也可以手动设置JOIN条件:

<?php

$phql = "SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands ON Brands.id = Cars.brands_id";

$rows = $manager->executeQuery($phql);

Also, the joins can be created using multiple tables in the FROM clause:

同时,可以创建连接在FROM子句中使用多个表:

<?php

$phql = "SELECT Cars.*, Brands.* FROM Cars, Brands WHERE Brands.id = Cars.brands_id";

$rows = $manager->executeQuery($phql);

foreach ($rows as $row) {
    echo "Car: ", $row->cars->name, "\n";
    echo "Brand: ", $row->brands->name, "\n";
}

If an alias is used to rename the models in the query, those will be used to name the attributes in the every row of the result:

如果使用别名来重命名模型查询中,那些将被用来命名属性的每一行的结果:

<?php

$phql = "SELECT c.*, b.* FROM Cars c, Brands b WHERE b.id = c.brands_id";

$rows = $manager->executeQuery($phql);

foreach ($rows as $row) {
    echo "Car: ", $row->c->name, "\n";
    echo "Brand: ", $row->b->name, "\n";
}

When the joined model has a many-to-many relation to the ‘from’ model, the intermediate model is implicitly added to the generated query:

当加入模型有一个“从”模型的多对多关系,中间模型隐式地添加到生成的查询:

<?php

$phql = "SELECT Artists.name, Songs.name FROM Artists " .
        "JOIN Songs WHERE Artists.genre = 'Trip-Hop'";

$result = $this->modelsManager->executeQuery($phql);

This code executes the following SQL in MySQL:

这段代码执行以下SQL在MySQL中:

SELECT `artists`.`name`, `songs`.`name` FROM `artists`
INNER JOIN `albums` ON `albums`.`artists_id` = `artists`.`id`
INNER JOIN `songs` ON `albums`.`songs_id` = `songs`.`id`
WHERE `artists`.`genre` = 'Trip-Hop'

聚合（Aggregations）

The following examples show how to use aggregations in PHQL:

下面的例子展示如何使用PHQL聚合:

<?php

// How much are the prices of all the cars?
$phql = "SELECT SUM(price) AS summatory FROM Cars";
$row  = $manager->executeQuery($phql)->getFirst();
echo $row['summatory'];

// How many cars are by each brand?
$phql = "SELECT Cars.brand_id, COUNT(*) FROM Cars GROUP BY Cars.brand_id";
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
    echo $row->brand_id, ' ', $row["1"], "\n";
}

// How many cars are by each brand?
$phql = "SELECT Brands.name, COUNT(*) FROM Cars JOIN Brands GROUP BY 1";
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
    echo $row->name, ' ', $row["1"], "\n";
}

$phql = "SELECT MAX(price) AS maximum, MIN(price) AS minimum FROM Cars";
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
    echo $row["maximum"], ' ', $row["minimum"], "\n";
}

// Count distinct used brands
$phql = "SELECT COUNT(DISTINCT brand_id) AS brandId FROM Cars";
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
    echo $row->brandId, "\n";
}

条件（Conditions）

Conditions allow us to filter the set of records we want to query. The WHERE clause allows to do that:

查询条件允许我们过滤查询我们想要的记录。WHERE子句允许这样做:

<?php

// Simple conditions
$phql = "SELECT * FROM Cars WHERE Cars.name = 'Lamborghini Espada'";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.price > 10000";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE TRIM(Cars.name) = 'Audi R8'";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.name LIKE 'Ferrari%'";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.name NOT LIKE 'Ferrari%'";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.price IS NULL";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.id IN (120, 121, 122)";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.id NOT IN (430, 431)";
$cars = $manager->executeQuery($phql);

$phql = "SELECT * FROM Cars WHERE Cars.id BETWEEN 1 AND 100";
$cars = $manager->executeQuery($phql);

Also, as part of PHQL, prepared parameters automatically escape the input data, introducing more security:

作为PHQL,准备参数自动转义输入数据,引入更多的安全机制:

<?php

$phql = "SELECT * FROM Cars WHERE Cars.name = :name:";
$cars = $manager->executeQuery(
    $phql,
    [
        "name" => "Lamborghini Espada"
    ]
);

$phql = "SELECT * FROM Cars WHERE Cars.name = ?0";
$cars = $manager->executeQuery(
    $phql,
    [
        0 => "Lamborghini Espada"
    ]
);

插入数据（Inserting Data）

With PHQL it’s possible to insert data using the familiar INSERT statement:

与PHQL可以使用熟悉的insert语句插入数据:

<?php

// Inserting without columns
$phql = "INSERT INTO Cars VALUES (NULL, 'Lamborghini Espada', "
      . "7, 10000.00, 1969, 'Grand Tourer')";
$manager->executeQuery($phql);

// Specifying columns to insert
$phql = "INSERT INTO Cars (name, brand_id, year, style) "
      . "VALUES ('Lamborghini Espada', 7, 1969, 'Grand Tourer')";
$manager->executeQuery($phql);

// Inserting using placeholders
$phql = "INSERT INTO Cars (name, brand_id, year, style) "
      . "VALUES (:name:, :brand_id:, :year:, :style)";
$manager->executeQuery(
    $phql,
    [
        "name"     => "Lamborghini Espada",
        "brand_id" => 7,
        "year"     => 1969,
        "style"    => "Grand Tourer",
    ]
);

Phalcon doesn’t only transform the PHQL statements into SQL. All events and business rules defined in the model are executed as if we created individual objects manually. Let’s add a business rule on the model cars. A car cannot cost less than $ 10,000:

Phalcon不仅将PHQL语句转换成SQL。模型中定义的所有事件和业务规则执行,如同我们手动创建单个对象。让我们添加一个业务规则的模型汽车。一辆车不能成本不到10000美元。

<?php

use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Message;

class Cars extends Model
{
    public function beforeCreate()
    {
        if ($this->price < 10000) {
            $this->appendMessage(
                new Message("A car cannot cost less than $ 10,000")
            );

            return false;
        }
    }
}

If we made the following INSERT in the models Cars, the operation will not be successful because the price does not meet the business rule that we implemented. By checking the status of the insertion we can print any validation messages generated internally:

如果我们做了以下插入的汽车模型操作将不会成功,因为价格不符合业务规则实现。通过检查插入的状态我们可以打印任何验证内部产生的消息:

<?php

$phql = "INSERT INTO Cars VALUES (NULL, 'Nissan Versa', 7, 9999.00, 2015, 'Sedan')";

$result = $manager->executeQuery($phql);

if ($result->success() === false) {
    foreach ($result->getMessages() as $message) {
        echo $message->getMessage();
    }
}

更新数据（Updating Data）

Updating rows is very similar than inserting rows. As you may know, the instruction to update records is UPDATE. When a record is updated the events related to the update operation will be executed for each row.

更新行和插入行非常相似。正如你可能知道,更新记录的指令是UPDATE。当一个记录被更新,与更新操作相关的事件将为每一行执行更新操作。

<?php

// Updating a single column
$phql = "UPDATE Cars SET price = 15000.00 WHERE id = 101";
$manager->executeQuery($phql);

// Updating multiples columns
$phql = "UPDATE Cars SET price = 15000.00, type = 'Sedan' WHERE id = 101";
$manager->executeQuery($phql);

// Updating multiples rows
$phql = "UPDATE Cars SET price = 7000.00, type = 'Sedan' WHERE brands_id > 5";
$manager->executeQuery($phql);

// Using placeholders
$phql = "UPDATE Cars SET price = ?0, type = ?1 WHERE brands_id > ?2";
$manager->executeQuery(
    $phql,
    [
        0 => 7000.00,
        1 => 'Sedan',
        2 => 5,
    ]
);

An UPDATE statement performs the update in two phases:

  • First, if the UPDATE has a WHERE clause it retrieves all the objects that match these criteria,
  • Second, based on the queried objects it updates/changes the requested attributes storing them to the relational database

This way of operation allows that events, virtual foreign keys and validations take part of the updating process. In summary, the following code:

一个更新语句执行更新在两个阶段:

  • 首先,如果更新有一个WHERE子句检索符合这些标准的所有对象,
  • 其次,基于查询对象更新/更改请求的属性存储到关系数据库中

这种方式的操作允许事件,虚拟外键和验证更新过程的一部分。总之,下面的代码:

<?php

$phql = "UPDATE Cars SET price = 15000.00 WHERE id > 101";

$result = $manager->executeQuery($phql);

if ($result->success() === false) {
    $messages = $result->getMessages();

    foreach ($messages as $message) {
        echo $message->getMessage();
    }
}

is somewhat equivalent to:

某种程度上相当于:

<?php

$messages = null;

$process = function () use (&$messages) {
    $cars = Cars::find("id > 101");

    foreach ($cars as $car) {
        $car->price = 15000;

        if ($car->save() === false) {
            $messages = $car->getMessages();

            return false;
        }
    }

    return true;
};

$success = $process();

删除数据（Deleting Data）

When a record is deleted the events related to the delete operation will be executed for each row:

当一条记录删除相关的事件将为每一行执行删除操作:

<?php

// Deleting a single row
$phql = "DELETE FROM Cars WHERE id = 101";
$manager->executeQuery($phql);

// Deleting multiple rows
$phql = "DELETE FROM Cars WHERE id > 100";
$manager->executeQuery($phql);

// Using placeholders
$phql = "DELETE FROM Cars WHERE id BETWEEN :initial: AND :final:";
$manager->executeQuery(
    $phql,
    [
        "initial" => 1,
        "final"   => 100,
    ]
);

DELETE operations are also executed in two phases like UPDATEs. To check if the deletion produces any validation messages you should check the status code returned:

<?php

// Deleting multiple rows
$phql = "DELETE FROM Cars WHERE id > 100";

$result = $manager->executeQuery($phql);

if ($result->success() === false) {
    $messages = $result->getMessages();

    foreach ($messages as $message) {
        echo $message->getMessage();
    }
}

使用查询构建器创建查询（Creating queries using the Query Builder）

A builder is available to create PHQL queries without the need to write PHQL statements, also providing IDE facilities:

构建器可用来创建PHQL查询,而不需要编写PHQL语句,也提供IDE设备:

<?php

// Getting a whole set
$robots = $this->modelsManager->createBuilder()
    ->from("Robots")
    ->join("RobotsParts")
    ->orderBy("Robots.name")
    ->getQuery()
    ->execute();

// Getting the first row
$robots = $this->modelsManager->createBuilder()
    ->from("Robots")
    ->join("RobotsParts")
    ->orderBy("Robots.name")
    ->getQuery()
    ->getSingleResult();

That is the same as:

和这个是一样的:

<?php

$phql = "SELECT Robots.* FROM Robots JOIN RobotsParts p ORDER BY Robots.name LIMIT 20";

$result = $manager->executeQuery($phql);

More examples of the builder:

更多的生成器的例子:

<?php

// 'SELECT Robots.* FROM Robots';
$builder->from("Robots");

// 'SELECT Robots.*, RobotsParts.* FROM Robots, RobotsParts';
$builder->from(
    [
        "Robots",
        "RobotsParts",
    ]
);

// 'SELECT * FROM Robots';
$phql = $builder->columns("*")
                ->from("Robots");

// 'SELECT id FROM Robots';
$builder->columns("id")
        ->from("Robots");

// 'SELECT id, name FROM Robots';
$builder->columns(["id", "name"])
        ->from("Robots");

// 'SELECT Robots.* FROM Robots WHERE Robots.name = "Voltron"';
$builder->from("Robots")
        ->where("Robots.name = 'Voltron'");

// 'SELECT Robots.* FROM Robots WHERE Robots.id = 100';
$builder->from("Robots")
        ->where(100);

// 'SELECT Robots.* FROM Robots WHERE Robots.type = "virtual" AND Robots.id > 50';
$builder->from("Robots")
        ->where("type = 'virtual'")
        ->andWhere("id > 50");

// 'SELECT Robots.* FROM Robots WHERE Robots.type = "virtual" OR Robots.id > 50';
$builder->from("Robots")
        ->where("type = 'virtual'")
        ->orWhere("id > 50");

// 'SELECT Robots.* FROM Robots GROUP BY Robots.name';
$builder->from("Robots")
        ->groupBy("Robots.name");

// 'SELECT Robots.* FROM Robots GROUP BY Robots.name, Robots.id';
$builder->from("Robots")
        ->groupBy(["Robots.name", "Robots.id"]);

// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name';
$builder->columns(["Robots.name", "SUM(Robots.price)"])
    ->from("Robots")
    ->groupBy("Robots.name");

// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name HAVING SUM(Robots.price) > 1000';
$builder->columns(["Robots.name", "SUM(Robots.price)"])
    ->from("Robots")
    ->groupBy("Robots.name")
    ->having("SUM(Robots.price) > 1000");

// 'SELECT Robots.* FROM Robots JOIN RobotsParts';
$builder->from("Robots")
    ->join("RobotsParts");

// 'SELECT Robots.* FROM Robots JOIN RobotsParts AS p';
$builder->from("Robots")
    ->join("RobotsParts", null, "p");

// 'SELECT Robots.* FROM Robots JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p';
$builder->from("Robots")
    ->join("RobotsParts", "Robots.id = RobotsParts.robots_id", "p");

// 'SELECT Robots.* FROM Robots
// JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p
// JOIN Parts ON Parts.id = RobotsParts.parts_id AS t';
$builder->from("Robots")
    ->join("RobotsParts", "Robots.id = RobotsParts.robots_id", "p")
    ->join("Parts", "Parts.id = RobotsParts.parts_id", "t");

// 'SELECT r.* FROM Robots AS r';
$builder->addFrom("Robots", "r");

// 'SELECT Robots.*, p.* FROM Robots, Parts AS p';
$builder->from("Robots")
    ->addFrom("Parts", "p");

// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(["r" => "Robots"])
        ->addFrom("Parts", "p");

// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(["r" => "Robots", "p" => "Parts"]);

// 'SELECT Robots.* FROM Robots LIMIT 10';
$builder->from("Robots")
    ->limit(10);

// 'SELECT Robots.* FROM Robots LIMIT 10 OFFSET 5';
$builder->from("Robots")
        ->limit(10, 5);

// 'SELECT Robots.* FROM Robots WHERE id BETWEEN 1 AND 100';
$builder->from("Robots")
        ->betweenWhere("id", 1, 100);

// 'SELECT Robots.* FROM Robots WHERE id IN (1, 2, 3)';
$builder->from("Robots")
        ->inWhere("id", [1, 2, 3]);

// 'SELECT Robots.* FROM Robots WHERE id NOT IN (1, 2, 3)';
$builder->from("Robots")
        ->notInWhere("id", [1, 2, 3]);

// 'SELECT Robots.* FROM Robots WHERE name LIKE '%Art%';
$builder->from("Robots")
        ->where("name LIKE :name:", ["name" => "%" . $name . "%"]);

// 'SELECT r.* FROM Store\Robots WHERE r.name LIKE '%Art%';
$builder->from(['r' => 'Store\Robots'])
        ->where("r.name LIKE :name:", ["name" => "%" . $name . "%"]);

绑定参数（Bound Parameters）

Bound parameters in the query builder can be set as the query is constructed or past all at once when executing:

绑定参数query builder可以设置的查询构造或过去一次当执行:

<?php

// Passing parameters in the query construction
$robots = $this->modelsManager->createBuilder()
    ->from("Robots")
    ->where("name = :name:", ["name" => $name])
    ->andWhere("type = :type:", ["type" => $type])
    ->getQuery()
    ->execute();

// Passing parameters in query execution
$robots = $this->modelsManager->createBuilder()
    ->from("Robots")
    ->where("name = :name:")
    ->andWhere("type = :type:")
    ->getQuery()
    ->execute(["name" => $name, "type" => $type]);

禁止使用字面值（Disallow literals in PHQL）

Literals can be disabled in PHQL, this means that directly using strings, numbers and boolean values in PHQL strings will be disallowed. If PHQL statements are created embedding external data on them, this could open the application to potential SQL injections:

文字可以在PHQL被禁用,这意味着直接使用字符串、数字和布尔值PHQL字符串无效。如果PHQL语句创建嵌入外部数据,这可以打开应用程序潜在的SQL注入:

<?php

$login = 'voltron';

$phql = "SELECT * FROM Models\Users WHERE login = '$login'";

$result = $manager->executeQuery($phql);

If $login is changed to ' OR '' = ', the produced PHQL is:

如果改变$login 为”OR“=”,产生PHQL是:

SELECT * FROM Models\Users WHERE login = '' OR '' = ''

Which is always true no matter what the login stored in the database is.

If literals are disallowed strings can be used as part of a PHQL statement, thus an exception will be thrown forcing the developer to use bound parameters. The same query can be written in a secure way like this:

这始终是真正的不管什么是登录存储在数据库中存储的注册。
如果文字是不允许字符串可以用作PHQL声明的一部分,因此会抛出一个异常,迫使开发人员使用绑定参数。相同的查询可以用一种安全的方法是这样的:

<?php

$phql = "SELECT Robots.* FROM Robots WHERE Robots.name = :name:";

$result = $manager->executeQuery(
    $phql,
    [
        "name" => $name,
    ]
);

You can disallow literals in the following way:

<?php

use Phalcon\Mvc\Model;

Model::setup(
    [
        "phqlLiterals" => false
    ]
);

Bound parameters can be used even if literals are allowed or not. Disallowing them is just another security decision a developer could take in web applications.

可以使用绑定参数,即使文字是否允许。禁止他们只是另一个开发人员可以在web应用程序安全的决定。

转义保留字（Escaping Reserved Words）

PHQL has a few reserved words, if you want to use any of them as attributes or models names, you need to escape those words using the cross-database escaping delimiters ‘[‘ and ‘]’:

PHQL有一些保留字,如果你想使用任何属性或模型的名称,您需要使用跨数据库逃离逃离这些话分隔符“[”and“]”:

<?php

$phql   = "SELECT * FROM [Update]";
$result = $manager->executeQuery($phql);

$phql   = "SELECT id, [Like] FROM Posts";
$result = $manager->executeQuery($phql);

The delimiters are dynamically translated to valid delimiters depending on the database system where the application is currently running on.

分隔符是动态转换成有效的分隔符取决于应用程序正在运行的数据库系统。

PHQL 生命周期（PHQL Lifecycle）

Being a high-level language, PHQL gives developers the ability to personalize and customize different aspects in order to suit their needs. The following is the life cycle of each PHQL statement executed:

  • The PHQL is parsed and converted into an Intermediate Representation (IR) which is independent of the SQL implemented by database system
  • The IR is converted to valid SQL according to the database system associated to the model
  • PHQL statements are parsed once and cached in memory. Further executions of the same statement result in a slightly faster execution

作为一个高级的语言,PHQL使开发人员能够个性化和定制不同方面为了满足他们的需求。下面是每个PHQL语句执行的生命周期:

  • PHQL解析和转换成一个中间表示(IR)是独立的SQL数据库系统实现的
  • 根据红外转换为有效的SQL数据库系统相关的模型
  • PHQL语句解析一次,缓存在内存中。进一步执行相同的语句导致更快的执行

使用原生 SQL（Using Raw SQL）

A database system could offer specific SQL extensions that aren’t supported by PHQL, in this case, a raw SQL can be appropriate:

数据库系统可以提供特定的SQL扩展PHQL不支持,在这种情况下,可以适当的原始SQL:

<?php

use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Model
{
    public static function findByCreateInterval()
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE id > 0";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(
            null,
            $robot,
            $robot->getReadConnection()->query($sql)
        );
    }
}

If Raw SQL queries are common in your application a generic method could be added to your model:

如果原始SQL查询在您的应用程序中是很常见的一个通用的方法可以添加到您的模型:

<?php

use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Model
{
    public static function findByRawSql($conditions, $params = null)
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE $conditions";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(
            null,
            $robot,
            $robot->getReadConnection()->query($sql, $params)
        );
    }
}

The above findByRawSql could be used as follows:

上述findByRawSql可以使用如下:

<?php

$robots = Robots::findByRawSql(
    "id > ?",
    [
        10
    ]
);

注意事项（Troubleshooting）

Some things to keep in mind when using PHQL:

  • Classes are case-sensitive, if a class is not defined with the same name as it was created this could lead to an unexpected behavior in operating systems with case-sensitive file systems such as Linux.
  • Correct charset must be defined in the connection to bind parameters with success.
  • Aliased classes aren’t replaced by full namespaced classes since this only occurs in PHP code and not inside strings.
  • If column renaming is enabled avoid using column aliases with the same name as columns to be renamed, this may confuse the query resolver.

有些事情要记住当使用PHQL:

  • -类是区分大小写的,如果一个类没有定义创建具有相同名称的,这可能导致意外行为等区分大小写的文件系统的操作系统Linux。
  • -必须定义正确的字符集在连接与成功绑定参数。
  • 别名类没有被完整的名称空间类因为这只发生在PHP代码而不是字符串。
  • 如果启用了列重命名避免使用列别名和名称相同的列重命名,这可能混淆查询解析器。
欢迎使用优快云-markdown编辑器.md
09-24
2019最新csdn自带的介绍范文模板 如果写过一次博客，这个文档就找不到了（没有恢复到本模板的url） 就在这里备份一下 方便查阅
Linux(Ubuntu)使用日记------markdown文件与pdf,doc,docx文件的相互转化(pandoc使用)
weixin_30355437的博客
04-24 271
安装: sudoapt-getinstallpandoc 使用: manpandoc查看帮助文档 直接转换，命令如下： pandoc-fmarkdown-tdocx./test.md-otest.docx 如果使用GitHub风格markdown语法，可用下面命令转换： pandoc-fgfm-tdocx./test.md-otest...
欢迎使用优快云-markdown编辑器123
jackkke的博客
08-07 1167
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Mar
欢迎使用优快云-markdown编辑器-
tuzixini的博客
06-01 1114
欢迎使用Markdown编辑器写博客 本Markdown编辑器使用StackEdit修改而来，用它写博客，将会带来全新的体验哦： Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 UML序列图和流程图 离线写博客 导入导出Markdown文件 丰富的快捷键 快捷键 加粗 Ctrl + B 斜体 Ctrl + I...
欢迎 使用优快云-markdown编辑器
Anonymo的博客
01-07 530
Markdown编辑器优快云官方教程 是不是第一次用过优快云的MarkDown编辑器之后就找不到官方的教程了，想写又不知道怎么写，别怕，我截屏了。
欢迎使用优快云-markdown编辑器- 简书
Andy_ZJH的博客
03-26 1068
欢迎使用优快云-markdown编辑器 96 流水潺湲 关注 2017.03.09 10:41* 字数 876 阅读 965评论 0喜欢 0 —来自优快云博客 欢迎使用Markdown编辑器写博客 本Markdown编辑器使用StackEdit修改而来，用它写博客，将会带来全新的体验哦： Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 U...
欢迎使用优快云-markdown编辑器(保存一下哈哈)
kikyou_csdn的博客
11-08 1742
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Ma...
----欢迎使用优快云-markdown编辑器
稻草人的博客
08-17 1198
欢迎使用Markdown编辑器写博客 本Markdown编辑器使用StackEdit修改而来，用它写博客，将会带来全新的体验哦： Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 UML序列图和流程图 离线写博客 导入导出Markdown文件 丰富的快捷键 快捷键 加粗 Ctrl + B 斜体 Ctrl + I...
如何使用优快云-markdown编辑器
小谢游乐园
09-28 1188
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Ma...
欢迎使用优快云-markdown编辑器.html
04-21
每个javascript对象都有一个原型对象，这个对象在不同的解释器下的实现不同。比如在firefox下，每个对象都有一个隐藏的__proto__属性，这个属性就是“原型对象”的引用。
电力系统调频中VSG技术对珍稀模型优化的应用研究
最新发布
07-25
内容概要：本文探讨了在电力系统调频过程中，传统火电机组由于阻尼不足而导致频率振荡的问题。针对这一现象，引入了虚拟同步发电机(VSG)技术作为解决方案。文中通过具体实例展示了VSG如何通过非线性阻尼项来改善系统的稳定性，并介绍了基于模型预测控制(MPC)的方法进行参数优化的具体步骤及其显著成效。 适合人群：从事电力系统研究的专业人士、高校相关专业师生以及对电力系统调频感兴趣的科研工作者。 使用场景及目标：适用于希望深入了解电力系统调频机制改进方法的研究人员和技术人员；旨在为解决现有电力系统中存在的调频难题提供理论依据和技术支持。 阅读建议：读者需要具备一定的电力工程基础知识，特别是对于同步发电机组的工作原理有所了解。同时，在阅读过程中可以重点关注VSG技术的具体应用方式以及参数调整的实际效果对比部分。
永磁同步电机双矢量模型预测控制的MATLAB仿真研究及应用
07-25
永磁同步电机（PMSM）双矢量模型预测控制（MPC）的研究背景、基本原理以及在MATLAB环境下的仿真过程。文章首先阐述了PMSM的工作原理，接着深入探讨了双矢量MPC的理论基础，包括其预测机制和优化指标。随后，作者展示了如何在MATLAB中构建PMSM的数学模型并实现双矢量MPC的控制算法。通过对仿真结果的分析，验证了双矢量MPC在提升PMSM动态响应速度和稳态精度方面的优越性能。最后，文章对未来的研究方向进行了展望，强调了进一步优化控制策略和技术发展的必要性。 适合人群：从事电机控制、自动化工程及相关领域的研究人员和技术人员。 使用场景及目标：适用于希望深入了解PMSM控制策略及其仿真实现的研究人员，旨在提高PMSM的控制性能，特别是在动态响应和稳态精度方面。 其他说明：本文不仅提供了详细的理论解释，还给出了具体的仿真步骤和结果分析，有助于读者更好地理解和掌握双矢量MPC的应用。
iOS系统优化-Objective-C与Swift混合开发-深度定制系统设置项与功能模块-为越狱iOS设备提供全面系统参数调节与性能优化工具-包含网络加速-电池管理-界面美化-权限.zip
07-25
iOS系统优化_Objective-C与Swift混合开发_深度定制系统设置项与功能模块_为越狱iOS设备提供全面系统参数调节与性能优化工具_包含网络加速_电池管理_界面美化_权限.zipCursor神操作编程创意赛
移动机器人运动控制仿真平台标题 V1 创建时间：16:36
07-25
资源下载链接为： https://pan.quark.cn/s/7bbf40e660d4 要运行各类示例，需确保目录中包含以下文件：remApi.m、remoteApiProto.m，以及对应的远程 API 库（Windows 系统为 "remoteApi.dll"，Mac 系统为 "remoteApi.dylib"，Linux 系统为 "remoteApi.so"），还有 simpleTest.m（或其他示例程序）。 若选择不使用原型文件 "remoteApiProto.m"，则必须确保已为 Matlab 配置好编译器（通过 mex -setup 操作）。同时，文件夹中需要有 "extApi.h" 文件，并且实例化远程 API 时需使用 "sim=remApi ('remoteApi','extApi.h');"，而非 "sim=remApi ('remoteApi');"。 如果希望重新生成原型文件，需先满足上述条件，然后输入命令：loadlibrary ('remoteApi','extApi.h','mfilename','remoteApiProto')。 如需更多示例，可查看 python 文件夹，尽管编程语言不同，但原理是一致的。
MDN/content -Github
07-25
The content behind MDN Web Docs. MDN Web Docs是一个开源协作项目，记录了包括 CSS、HTML、JavaScript 和 Web API 在内的各种 Web 技术。除了详细的参考文档外，我们还为学生和 Web 开发初学者提供了丰富的学习资源。 yarn start 即可实时预览
深入理解计算机系统课程实验全解析与CMU15213CSAPP实验题完整解决方案-计算机系统基础实验CMU15213课程CSAPP实验位操作实验缓冲区溢出实验性能.zip
07-25
张大头42步进闭环深入理解计算机系统课程实验全解析与CMU15213CSAPP实验题完整解决方案_计算机系统基础实验CMU15213课程CSAPP实验位操作实验缓冲区溢出实验性能.zip
西门子1200 PLC博途程序实例解析：气力输灰系统与配料控制的应用及代码分析
07-25
西门子1200 PLC在工业自动化领域的两个典型应用实例——气力输灰系统和配料控制系统。气力输灰系统通过单排5个AV料泵的输送程序，实现对灰粉等散装物料的自动化处理，涵盖料泵的启动、停止、运行状态控制及与昆仑通泰触摸屏的实时交互。配料控制系统则通过配方程序和变频通讯程序，精确控制物料的配料比例和输送速度，支持配方设定和修改。文中还详细分析了各个程序模块的具体实现方法及其背后的代码逻辑。 适合人群：从事工业自动化领域的工程师和技术人员，尤其是对西门子1200 PLC编程感兴趣的读者。 使用场景及目标：帮助工程师掌握西门子1200 PLC在实际项目中的应用技巧，提升编程能力和解决复杂工业控制问题的能力。 其他说明：通过对这两个实例的学习，读者不仅可以深入了解PLC的工作原理，还能掌握与触摸屏和其他设备的通信方式，从而更好地应用于实际工作中。
优快云-markdown编辑器
04-25
优快云 Markdown编辑器是一款由优快云自主开发的在线Markdown编辑工具，它具有以下特点和功能： 1. 简洁易用：优快云 Markdown编辑...7. 多平台支持：优快云 Markdown编辑器可以在多个平台上使用，包括网页版、移动端应用等。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

银色种子

打赏 >100 请留言，并私信

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值