Phalcon的查询语言 (PHQL)

Phalcon查询语言、PhalconQL或简单的PHQL是一种高级的、面向对象的SQL方言，允许使用标准化的类似SQL的语言编写查询。 PHQL实现为解析器(用C语言编写)，将语法转换为目标RDBMS的语法。

为了实现尽可能高的性能, phalcon 提供了一个使用与 SQLite 相同技术的解析器。这项技术提供了一个小的内存解析器, 内存占用非常低, 也是线程安全的。

解析器首先检查 pass PHQL 语句的语法, 然后生成语句的中间表示形式, 最后将其转换为目标 rdbms 的相应 sql 方言。

在 PHQL 中, 我们实现了一组功能, 使您对数据库的访问更加安全:

绑定参数是 PHQL语言的一部分, 可帮助您保护代码
PHQL只允许每个调用执行一个 sql 语句, 以防止注入
PHQL忽略 sql 注入中经常使用的所有 sql 注释
PHQL只允许数据操作语句, 避免错误地更改或删除表数据库或未经授权在外部更改或删除
PHQL 实现了一个高级抽象, 允许您将表作为模型处理, 将字段作为类属性处理

用法示例

To better explain how PHQL works consider the following example. We have two models 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');
    }
}

每辆车都有一个品牌，所以一个品牌有很多车:

<?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 查询

PHQL queries can be created just by instantiating the class Phalcon\Mvc\Model\Query:

<?php

use Phalcon\Mvc\Model\Query;

// 实例化查询
$query = new Query(
    'SELECT * FROM Cars',
    $this->getDI()
);

// 执行返回任何结果的查询
$cars = $query->execute();

From a controller or a view, it’s easy to create/execute them using an injected models manager (Phalcon\Mvc\Model\Manager):

<?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',
    ]
);

查询记录

作为熟悉的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);

大多数SQL标准都由PHQL支持，甚至是非标准指令，如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);

结果类型

根据我们查询的列的类型，结果类型会有所不同。 If you retrieve a single whole object, then the object returned is a Phalcon\Mvc\Model\Resultset\Simple. 这种resultset是一组完整的模型对象:

<?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";
}

We are only requesting some fields in the table, therefore those cannot be considered an entire object, so the returned object is still a resultset of type 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..:

<?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";
}

因为我们可以查询完整的对象或标量，我们也可以同时查询:

<?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:

<?php

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

”标量映射为每个“行”的属性，而完整对象映射为具有相关模型名称的属性。

Joins

It’s easy to request records from multiple models using PHQL. Most kinds of Joins are supported. As we defined relationships in the models, PHQL adds these conditions automatically:

<?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);

也可以手动设置连接的条件:

<?php

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

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

另外，可以使用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";
}

如果一个别名用于重命名查询中的模型，那么这些别名将用于命名结果的每一行中的属性:

<?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";
}

当连接的模型与模型有多对多关系时，中间模型隐式地添加到生成的查询中:

<?php

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

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

此代码在MySQL中执行以下SQL:

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'

聚合

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

<?php

// 所有汽车的价格是多少?
$phql = 'SELECT SUM(price) AS summatory FROM Cars';
$row  = $manager->executeQuery($phql)->getFirst();
echo $row['summatory'];

// 每个品牌有多少辆车?
$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";
}

// 每个品牌有多少辆车?
$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";
}

// 统计不同的品牌
$phql = 'SELECT COUNT(DISTINCT brand_id) AS brandId FROM Cars';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
    echo $row->brandId, "\n";
}

条件

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

<?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);

同时，作为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'
    ]
);

插入数据

使用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不只是将PHQL语句转换成SQL。模型中定义的所有事件和业务规则都像手动创建单个对象一样执行。让我们在模型汽车上添加一个业务规则。一辆车的价格不能低于1万美元:

<?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;
        }
    }
}

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

<?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();
    }
}

更新数据

更新行与插入行非常相似。您可能知道，更新记录的指令是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,
    ]
);

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

首先，如果UPDATE具有WHERE子句检索所有符合这些条件的对象，
其次，基于查询对象，它更新/更改将其存储到关系数据库的请求属性

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

<?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();
    }
}

相当于:

<?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();

删除数据

当一个记录被删除时，与删除操作相关的事件将对每一行执行:

<?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();
    }
}

使用查询生成器创建查询

构建器可以创建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();

这等于:

<?php

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

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

更多的构建器例子:

<?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 . '%']);

绑定参数

查询生成器中的绑定参数可以在查询被构造时设置，或者在执行时一次性全部超过:

<?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]);

PHQL不允许的文字

在PHQL中可以禁用文字，这意味着直接使用字符串、数字和布尔值将被禁用。如果创建了将外部数据嵌入到PHQL语句上的PHQL语句，这可能会使应用程序面临潜在的SQL注入:

<?php

$login  = 'voltron';
$phql   = "SELECT * FROM Models\Users WHERE login = '$login'";
$result = $manager->executeQuery($phql);

如果$login 被更改为 ' OR '' = ', name生成的PHQL就是:

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

无论数据库中存储的登录名是什么，它总是true。

如果不允许字面值的字符串可以作为PHQL语句的一部分使用，那么就会抛出一个异常，迫使开发人员使用绑定参数。可以以安全的方式编写相同的查询, 如下所示:

<?php

$type   = 'virtual';
$phql   = 'SELECT Robots.* FROM Robots WHERE Robots.type = :type:';
$result = $manager->executeQuery(
    $phql,
    [
        'type' => $type,
    ]
);

您可以通过以下方式不允许文本:

<?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.

转义保留字

PHQL 有几个保留字, 如果要将其中任何一个用作属性或模型名称, 则需要使用跨数据库转义分隔符 ` [ 和 ] ` 来转义这些单词:

<?php

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

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

根据应用程序当前运行的数据库系统，将分隔符动态转换为有效的分隔符。

PHQL生命周期

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

对 PHQL 进行解析并转换为中间表示 (IR), 该表示独立于数据库系统实现的 sql
IR 根据与模型关联的数据库系统转换为有效的 sql
PHQL statements are parsed once and cached in memory. Further executions of the same statement result in a slightly faster execution

使用原始的SQL

数据库系统可以提供 PHQL 不支持的特定 sql 扩展, 在这种情况下, 原始 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)
        );
    }
}

如果原始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)
        );
    }
}

以上findByRawSql可使用如下:

<?php

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

疑难解答

使用PHQL时要记住的一些事情:

类是区分大小写的，如果一个类在创建时没有使用相同的名称定义，这可能会导致在具有区分大小写文件系统(如Linux) 的操作系统中出现意外行为。
必须在连接中定义正确的字符集，才能成功绑定参数。
别名类不会被完全带名称空间的类所替代，因为这只发生在PHP代码中，而不在字符串中。
如果启用了列重命名，避免使用与要重命名的列同名的列别名，这可能会使查询解析器感到困惑。

General

Release Notes

Upgrade