跳转到内容

Phalcon 查询语言 (PHQL)

概览

Phalcon 查询语言，PhalconQL，或简称 PHQL 是一种高级的、面向对象的 SQL 方言，它允许你使用一种标准化类 SQL 的语言来编写查询语句。PHQL 作为一个解析器实现（用 C 编写），该解析器将语法翻译成目标 RDBMS 所使用的语法。

为了获得尽可能高的性能，Phalcon 提供了一个使用与SQLite相同技术的解析器。这项技术提供了一个内存占用非常低的小型内存内解析器，并且是线程安全的。

解析器首先检查要解析的 PHQL 语句的语法，然后构建该语句的中间表示形式，最后将其转换为目标 RDBMS 的相应 SQL 方言。

在 PHQL 中，我们实现了一组特性以使你的数据库访问更加安全：

  • 绑定参数是 PHQL 语言的一部分，帮助你保护代码的安全
  • PHQL 每次调用只允许执行一条 SQL 语句，以防止注入攻击
  • PHQL 忽略所有常用于 SQL 注入中的 SQL 注释
  • PHQL 仅允许数据操作语句，避免未经授权的情况下意外或通过外部方式修改或删除表/数据库
  • PHQL 实现了一个高级抽象层，让你可以将表视为模型，将字段视为类属性

为了更好地说明 PHQL 的工作原理，在本文中我们将使用两个模型：Invoices和Customers:

<?php

namespace MyApp\Models;

use MyApp\Models\Customers;
use Phalcon\Mvc\Model;

class Invoices extends Model
{
    public $inv_cst_id;

    public $inv_id;

    public $inv_status_flag;

    public $inv_title;

    public $inv_created_at;

    public function initialize()
    {
        $this->setSource('co_invoices');

        $this->belongsTo(
            'inv_cst_id', 
            Customers::class, 
            'cst_id'
        );
    }
}

每个客户可以拥有一张或多张发票：

<?php

namespace MyApp\Models;

use MyApp\Models\Invoices;
use Phalcon\Mvc\Model;

class Customers extends Model
{
    public $cst_id;

    public $cst_active_flag;

    public $cst_name_last;

    public $cst_name_first;

    public $cst_created_at;

    public function initialize()
    {
        $this->setSource('co_customers');

        $this->hasMany(
            'cst_id', 
            Invoices::class, 
            'inv_cst_id'
        );
    }
}

Query

PHQL 查询可以通过实例化类直接创建：Phalcon\Mvc\Model\Query:

<?php

use Phalcon\Mvc\Model\Query;

$container = Di::getDefault();
$query     = new Query(
    'SELECT * FROM Invoices',
    $container
);

$invoices = $query->execute();

The Phalcon\Mvc\Model\Query需要构造函数的第二个参数为 DI 容器。当你在控制器或任何继承自Phalcon\Di\Injectable的类中调用上述代码时，你可以这样使用：

<?php

use Phalcon\Di\Di;
use Phalcon\Mvc\Controller;
use Phalcon\Mvc\Model\Query;
use Phalcon\Mvc\View;

/**
 * @property Di   $di
 * @property View $view
 */
class Invoices extends Controller
{
    public function listAction()
    {
        $query = new Query(
            'SELECT * FROM Invoices',
            $this->di
        );

        $invoices = $query->execute();

        $this->view->setVar('invoices', $invoices);
    }
}

模型管理器

我们还可以使用已注入到 DI 容器中的Phalcon\Mvc\Model\Manager当前注入的 DI 容器：

<?php

use Phalcon\Mvc\Controller;
use Phalcon\Mvc\Model\Manager;
use Phalcon\Mvc\View;

/**
 * @property Manager $modelsManager
 * @property View    $view
 */
class Invoices extends Controller
{
    public function listAction()
    {
        $query = $this
            ->modelsManager
            ->createQuery(
                'SELECT * FROM Invoices'
            )
        ;

        $invoices = $query->execute();

        $this->view->setVar('invoices', $invoices);
    }
}

使用绑定参数：

<?php

use Phalcon\Http\Request;
use Phalcon\Mvc\Controller;
use Phalcon\Mvc\Model\Manager;
use Phalcon\Mvc\View;

/**
 * @property Manager $modelsManager
 * @property Request $request
 * @property View    $view
 */
class Invoices extends Controller
{
    public function viewAction()
    {
        $invoiceId = $this->request->getQuery('id', 'int');
        $query     = $this
            ->modelsManager
            ->createQuery(
                'SELECT * FROM Invoices WHERE inv_id = :id:'
            )
        ;

        $invoices = $query->execute(
            [
                'id' => $invoiceId,
            ]
        );

        $this->view->setVar('invoices', $invoices);
    }
}

你也可以跳过创建查询然后再执行它的步骤，而是直接通过模型管理器对象执行查询：

<?php

use Phalcon\Mvc\Controller;
use Phalcon\Mvc\Model\Manager;
use Phalcon\Mvc\View;

/**
 * @property Manager $modelsManager
 * @property View    $view
 */
class Invoices extends Controller
{
    public function listAction()
    {
        $invoices = $this
            ->modelsManager
            ->executeQuery(
                'SELECT * FROM Invoices'
            )
        ;

        $this->view->setVar('invoices', $invoices);
    }
}

使用绑定参数：

<?php

use Phalcon\Http\Request;
use Phalcon\Mvc\Controller;
use Phalcon\Mvc\Model\Manager;
use Phalcon\Mvc\View;

/**
 * @property Manager $modelsManager
 * @property Request $request
 * @property View    $view
 */
class Invoices extends Controller
{
    public function viewAction()
    {
        $invoiceId = $this->request->getQuery('id', 'int');
        $invoices  = $this
            ->modelsManager
            ->executeQuery(
                'SELECT * FROM Invoices WHERE inv_id = :id:',
                [
                    'id' => $invoiceId,
                ]
            )
        ;

        $this->view->setVar('invoices', $invoices);
    }
}

Select

与熟悉的 SQL 一样，PHQL 允许使用SELECT语句选择记录，不同的是我们使用模型类而不是指定表名：

模型

SELECT 
    * 
FROM   
    Invoices  
ORDER BY 
    Invoices.inv_title

SELECT 
    Invoices.inv_id, 
    Invoices.inv_title, 
    Invoices.inv_status_flag
FROM   
    Invoices  
ORDER BY 
    Invoices.inv_title

命名空间模型

SELECT 
    * 
FROM   
    MyApp\Models\Invoices
ORDER BY 
    MyApp\Models\Invoices.inv_title'

别名

SELECT 
    i.inv_id, 
    i.inv_title, 
    i.inv_status_flag
FROM   
    Invoices i  
ORDER BY 
    i.inv_title

CASE

SELECT 
    i.inv_id, 
    i.inv_title, 
    CASE i.inv_status_flag
        WHEN 1 THEN 'Paid'
        WHEN 0 THEN 'Unpaid'
    END AS status_text
FROM   
    Invoices i
WHERE  
    i.inv_status_flag = 1  
ORDER BY 
    i.inv_title
LIMIT 100

LIMIT

SELECT 
    i.inv_id, 
    i.inv_title, 
    i.inv_status_flag
FROM   
    Invoices i
WHERE  
    i.inv_status_flag = 1  
ORDER BY 
    i.inv_title
LIMIT 100

子查询

PHQL 也支持子查询。语法与 PDO 提供的语法类似。

SELECT 
    i.inv_id 
FROM   
    Invoices i
WHERE EXISTS (  
    SELECT 
        cst_id
    FROM
        Customers c
    WHERE 
        c.cst_id = i.inv_cst_id
)

SELECT 
    inv_id 
FROM   
    Invoices 
WHERE inv_cst_id IN (  
    SELECT 
        cst_id
    FROM
        Customers 
    WHERE 
        cst_name LIKE '%ACME%'
)

结果

根据我们查询的列以及表的不同，结果类型也会有所不同。

如果你从单张表中检索全部列，你将得到一个功能完整的Phalcon\Mvc\Model\Resultset\Simple对象。返回的对象是一个完整对象可以对其进行修改并重新保存至数据库，因为它代表了关联表的一条完整记录。

下面的例子返回相同的结果：

模型

<?php

use MyApp\Models\Invoices;

$invoices = Invoices::find(
    [
        'order' => 'inv_title'
    ]
);

foreach ($invoices as $invoice) {
    echo $invoice->inv_id, ' - ', $invoice->inv_name, PHP_EOL;
}

PHQL

<?php

$phql = "
    SELECT 
        * 
    FROM 
        Invoices 
    ORDER BY 
        inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

foreach ($invoices as $invoice) {
    echo $invoice->inv_id, ' - ', $invoice->inv_name, PHP_EOL;
}

任何使用特定列的查询都不会返回完整对象因此无法对它们执行数据库操作。然而，它们比完整的对象更小，可以在代码中进行微优化。

<?php

$phql = "
    SELECT 
        inv_id, inv_title 
    FROM 
        Invoices 
    ORDER BY 
        inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

foreach ($invoices as $invoice) {
    echo $invoice->inv_id, ' - ', $invoice->inv_name, PHP_EOL;
}

返回的结果是一个Phalcon\Mvc\Model\Resultset\Simple对象。不过，每个元素都是一个标准对象，只包含请求的两列。

这些不代表完整对象的值我们称之为标量（scalars）。PHQL 允许你查询所有类型的标量：字段、函数、字面量、表达式等：

<?php

$phql = "
    SELECT 
        CONCAT(inv_id, ' - ', inv_title) AS id_name 
    FROM 
        Invoices 
    ORDER BY 
        inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

foreach ($invoices as $invoice) {
    echo $invoice->id_name, PHP_EOL;
}

我们可以查询完整对象或者标量，因此也可以同时查询两者：

<?php

$phql = "
    SELECT 
        i.*, 
        IF(i.inv_status_flag = 1, 'Paid', 'Unpaid') AS status 
    FROM 
        Invoices i 
    ORDER BY 
        i.inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

此种情况下的结果是一个Phalcon\Mvc\Model\Resultset\Complex对象。这使得我们可以同时访问完整对象和标量：

<?php

foreach ($invoices as $invoice) {
    echo $invoice->status, 
         $invoice->i->inv_id, 
         $invoice->i->inv_name, 
         PHP_EOL
    ;
}

标量被映射为每行的标准属性，而完整对象则被映射为关联模型名称的属性。在上述例子中，标量status可以直接从对象访问，而数据库行可以通过invoices属性访问，其名称与模型名称一致。

如果你混合了*来自一个模型的选择项与来自另一个模型的列，你最终将得到标量和对象。

<?php

$phql = "
    SELECT 
        i.*, 
        IF(i.inv_status_flag = 1, 'Paid', 'Unpaid') AS status
        c.* 
    FROM 
        Invoices i
    JOIN
        Customers c
    ON
        i.inv_cst_id = c.cst_id 
    ORDER BY 
        i.inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

上述示例将生成：

<?php

foreach ($invoices as $invoice) {
    echo $invoice->status, 
         $invoice->i->inv_id, 
         $invoice->i->inv_name, 
         $invoice->c->cst_id, 
         $invoice->c->cst_name_last, 
         PHP_EOL
    ;
}

另一个例子：

<?php

$phql = "
    SELECT 
        i.*, 
        c.cst_name_last AS name_last 
    FROM 
        Invoices i
    JOIN
        Customers c
    ON
        i.inv_cst_id = c.cst_id 
    ORDER BY 
        i.inv_title";
$invoices  = $this
    ->modelsManager
    ->executeQuery($phql)
;

上述示例将生成：

<?php

foreach ($invoices as $invoice) {
    echo $invoice->name_last, 
         $invoice->i->inv_id, 
         $invoice->i->inv_name, 
         PHP_EOL
    ;
}

注意我们从Customers模型中选择了一列，并且我们需要对其设置别名 (name_last)，以便使其成为结果集中的标量。

连接（Joins）

使用 PHQL 很容易从多个模型中请求记录。大多数类型的连接都得到了支持。由于我们在模型中定义了关系，PHQL 会自动添加这些条件：

<?php

$phql = "
    SELECT 
        Invoices.inv_id AS invoice_id, 
        Invoices.inv_title AS invoice_title, 
        Customers.cst_id AS customer_id,
        Customers.cst_name_last,
        Customers.cst_name_first 
    FROM 
        Customers
    INNER JOIN 
        Invoices 
    ORDER BY 
        Customers.cst_name_last, Customers.cst_name_first";
$records  = $this
    ->modelsManager
    ->executeQuery($phql)
;

foreach ($records as $record) {
    echo $record->invoice_id, 
         $record->invoice_title, 
         $record->customer_id,
         $record->cst_name_last,
         $record->cst_name_first, 
         PHP_EOL
    ;
}

注意

默认情况下，假定使用的是INNER JOIN内连接（inner join）

你可以在查询中指定以下类型的连接：

  • CROSS JOIN
  • LEFT JOIN
  • LEFT OUTER JOIN
  • INNER JOIN
  • JOIN
  • RIGHT JOIN
  • RIGHT OUTER JOIN

PHQL 解析器将根据每个模型的JOIN操作的条件。这些是对initialize()中设置的关系自动解析hasMany, hasOne, belongsTo等等。

的调用。JOIN:

<?php

$phql = "
    SELECT 
        Invoices.inv_id AS invoice_id, 
        Invoices.inv_title AS invoice_title, 
        Customers.cst_id AS customer_id,
        Customers.cst_name_last,
        Customers.cst_name_first 
    FROM 
        Customers
    INNER JOIN 
        Invoices
    ON 
        Customers.cst_id = Invoices.inv_cst_id 
    ORDER BY 
        Customers.cst_name_last, Customers.cst_name_first";

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

然而，也可以手动设置FROM clause, using the alternative join语法：

<?php

$phql = "
    SELECT 
        Invoices.*, 
        Customers.* 
    FROM 
        Customers, Invoices
    WHERE 
        Customers.cst_id = Invoices.inv_cst_id 
    ORDER BY 
        Customers.cst_name_last, Customers.cst_name_first";

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

foreach ($records as $record) {
    echo $record->invoices->inv_id, 
         $record->invoices->inv_title, 
         $record->customers->cst_id,
         $record->customers->cst_name_last,
         $record->customers->cst_name_first, 
         PHP_EOL
    ;
}

如果模型使用了别名，则结果集会使用这些别名来命名每一行中的属性：

<?php

$phql = "
    SELECT 
        i.*, 
        c.* 
    FROM 
        Customers c, Invoices i
    WHERE 
        c.cst_id = i.inv_cst_id 
    ORDER BY 
        c.cst_name_last, c.cst_name_first";

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

foreach ($records as $record) {
    echo $record->i->inv_id, 
         $record->i->inv_title, 
         $record->c->cst_id,
         $record->c->cst_name_last,
         $record->c->cst_name_first, 
         PHP_EOL
    ;
}

当连接的模型与from模型有多对多关系时，中间模型会被隐式地添加到生成的查询中。例如，我们有Invoices, InvoicesXProducts和Productsmodels:

<?php

$phql = "
    SELECT 
        Invoices.inv_id, 
        Invoices.inv_title, 
        Products.prd_id,
        Products.prd_title 
    FROM 
        Invoices
    JOIN
        Products
    WHERE 
        Invoices.inv_id = 1 
    ORDER BY 
        Products.prd_name";

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

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

SELECT 
    co_invoices.inv_id, 
    co_invoices.inv_title, 
    co_products.prd_id,
    co_products.prd_title 
FROM 
    co_invoices
JOIN
    co_invoices_x_products 
ON 
    co_invoices.inv_id = co_invoices_x_products.ixp_inv_id
JOIN
    co_products 
ON 
    co_invoices_x_products.ixp_prd_id = co_products.prd_id
WHERE
    co_invoices.inv_id = 1
ORDER BY
    co_products.prd_name

聚合

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

平均值

客户的发票平均金额是多少inv_cst_id = 1

<?php

$phql = "
    SELECT 
        AVERAGE(inv_total) AS invoice_average
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = 1";

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

echo $results['invoice_average'], PHP_EOL;

计数

每个客户有多少张发票

<?php

$phql = "
    SELECT 
        inv_cst_id,
        COUNT(*) AS invoice_count
    FROM 
        Invoices
    GROUP BY 
        Invoices.inv_cst_id
    ORDER BY 
        Invoices.inv_cst_id";

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

foreach ($records as $record) {
    echo $record->inv_cst_id, 
         $record->invoice_count, 
         PHP_EOL
    ;
}

去重统计

每个客户有多少张发票

<?php

$phql = "
    SELECT 
        COUNT(DISTINCT inv_cst_id) AS customer_id
    FROM 
        Invoices
    ORDER BY 
        Invoices.inv_cst_id";

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

foreach ($records as $record) {
    echo $record->inv_cst_id, 
         PHP_EOL
    ;
}

最大值

客户的最大发票金额是多少inv_cst_id = 1

<?php

$phql = "
    SELECT 
        MAX(inv_total) AS invoice_max
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = 1";

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

echo $results['invoice_max'], PHP_EOL;

最小值

客户的最小发票金额是多少inv_cst_id = 1

<?php

$phql = "
    SELECT 
        MIN(inv_total) AS invoice_min
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = 1";

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

echo $results['invoice_min'], PHP_EOL;

求和

客户的发票总金额是多少inv_cst_id = 1

<?php

$phql = "
    SELECT 
        SUM(inv_total) AS invoice_total
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = 1";

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

echo $results['invoice_total'], PHP_EOL;

条件

条件允许我们使用WHERE关键字来过滤我们想要查询的记录集。

使用单一数字比较选择一条记录：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = 1";

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

使用大于比较选择多条记录：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_total > 1000";

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

使用单一文本比较选择记录：TRIM:

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        TRIM(Invoices.inv_title) = 'Invoice for ACME Inc.'";

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

使用LIKE关键字选择记录：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_title LIKE '%ACME%'";

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

使用NOT LIKE关键字：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_title NOT LIKE '%ACME%'";

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

选择字段为NULL:

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_total IS NULL";

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

使用IN关键字选择记录：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id IN (1, 3, 5)";

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

使用NOT IN关键字：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id NOT IN (1, 3, 5)";

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

使用BETWEEN关键字：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id BETWEEN 1 AND 5";

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

参数

PHQL自动转义参数，提高了安全性：

使用命名参数：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = :customer_id:";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            'customer_id' => 1,
        ]
    )
;

使用数字索引：

<?php

$phql = "
    SELECT 
        *
    FROM 
        Invoices
    WHERE 
        Invoices.inv_cst_id = ?2";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            2 => 1,
        ]
    )
;

插入

使用PHQL可以插入数据，方式与熟悉的INSERT语句中的代码：

不指定列插入数据：

<?php

$phql = "
    INSERT INTO Invoices
    VALUES (
        NULL,
        1,
        0,
        'Invoice for ACME Inc.',
        0
    )";

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

指定列插入数据：

<?php

$phql = "
    INSERT INTO Invoices (
        inv_id,
        inv_cst_id,
        inv_status_flag,
        inv_title,
        inv_total
    )
    VALUES (
        NULL,
        1,
        0,
        'Invoice for ACME Inc.',
        0
    )";

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

使用命名占位符插入数据：

<?php

$phql = "
    INSERT INTO Invoices (
        inv_id,
        inv_cst_id,
        inv_status_flag,
        inv_title,
        inv_total
    )
    VALUES (
        :id:,
        :cst_id:,
        :status_flag:,
        :title:,
        :total:
    )";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            'id'          => NULL,
            'cst_id'      => 1,
            'status_flag' => 0,
            'title'       => 'Invoice for ACME Inc.',
            'total'       => 0
        ]
    )
;

使用数字占位符插入数据：

<?php

$phql = "
    INSERT INTO Invoices (
        inv_id,
        inv_cst_id,
        inv_status_flag,
        inv_title,
        inv_total
    )
    VALUES (
        ?0,
        ?1,
        ?2,
        ?3,
        ?4
    )";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            0 => NULL,
            1 => 1,
            2 => 0,
            3 => 'Invoice for ACME Inc.',
            4 => 0
        ]
    )
;

Phalcon不仅仅将PHQL语句转换为SQL。模型中定义的所有事件和业务规则都会像我们手动创建每个对象一样被执行。

如果我们在beforeCreate事件中添加了某个Invoices模型的业务规则，该事件将会被调用并且我们的代码会被执行。假设我们添加了一条发票不能有负的总额的规则：

<?php

namespace MyApp\Models;

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

class Invoices extends Model
{
    public function beforeCreate()
    {
        if ($this->inv_total < 0) {
            $this->appendMessage(
                new Message('An invoice cannot have a negative total')
            );

            return false;
        }
    }
}

如果我们运行以下INSERT语句中的代码：

<?php

$phql = "
    INSERT INTO Invoices (
        inv_id,
        inv_cst_id,
        inv_status_flag,
        inv_title,
        inv_total
    )
    VALUES (
        ?0,
        ?1,
        ?2,
        ?3,
        ?4
    )";

$result  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            0 => NULL,
            1 => 1,
            2 => 0,
            3 => 'Invoice for ACME Inc.',
            4 => -100
        ]
    )
;

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

因为我们试图插入一个负数作为inv_total超全局变量。如果cookie存在于任一集合中，则返回beforeCreate在保存记录之前被调用。因此操作失败，相关的错误信息会被返回。

更新

更新行使用与插入行相同的规则。对于该操作，我们使用UPDATE命令。和插入行时一样，更新记录时会为每一行执行与更新操作相关的事件。

更新单个列

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_total = 0
    WHERE
        inv_cst_id = 1";

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

更新多个列

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_status_flag = 0,
        inv_total = 0
    WHERE
        inv_cst_id = 1";

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

更新多行：

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_status_flag = 0,
        inv_total = 0
    WHERE
        inv_cst_id > 10";

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

使用命名占位符更新数据：

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_status_flag = :status:,
        inv_total = :total:
    WHERE
        inv_cst_id > :customerId:";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            'status'     => 0,
            'total'      => 0,
            'customerId' => 10,
        ]
    )
;

使用数字占位符更新数据：

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_status_flag = ?0,
        inv_total = ?1
    WHERE
        inv_cst_id > ?2";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            0 => 0,
            1 => 0,
            2 => 10,
        ]
    )
;

An UPDATE语句分两个阶段执行更新：

  • 如果UPDATE包含WHERE子句时，它检索匹配这些条件的所有对象，
  • 基于查询到的对象，它更新请求的属性并将其存储到数据库中

这种操作方式允许在更新过程中执行事件、虚拟外键和验证。简而言之，代码：

<?php

$phql = "
    UPDATE Invoices
    SET
        inv_status_flag = 0,
        inv_total = 0
    WHERE
        inv_cst_id > 10";

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

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

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

大致等价于：

<?php

use MyApp\Models\Invoices;

$messages = [];
$invoices = Invoices::find(
    [
        'conditions' => 'inc_cst_id = :customerId:',
        'bind'       => [
            'customerId' => 10,
        ],
    ]  
);

foreach ($invoices as $invoice) {
    $invoice->inv_status_flag = 0;
    $invoice->inv_total       = 0;

    $result = $invoice->save();
    if (false === $result) {
        $messages[] = $invoice->getMessages();
    } 
}

删除数据

类似于更新记录，删除记录也使用相同的规则。对于该操作，我们使用DELETE命令。当记录被删除时，与更新操作相关的事件将对每一行执行。

删除一行

<?php

$phql = "
    DELETE
    FROM 
        Invoices
    WHERE
        inv_cst_id = 1";

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

删除多行：

<?php

$phql = "
    DELETE
    FROM 
        Invoices
    WHERE
        inv_cst_id > 10";

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

使用命名占位符删除数据：

<?php

$phql = "
    DELETE
    FROM 
        Invoices
    WHERE
        inv_cst_id > :customerId:";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            'customerId' => 10,
        ]
    )
;

使用数字占位符删除数据：

<?php

$phql = "
    DELETE
    FROM 
        Invoices
    WHERE
        inv_cst_id > ?2";

$records  = $this
    ->modelsManager
    ->executeQuery(
        $phql,
        [
            2 => 10,
        ]
    )
;

A DELETE语句分两个阶段执行删除：

  • 如果DELETE包含WHERE子句时，它检索匹配这些条件的所有对象，
  • 基于查询到的对象，它从关系数据库中删除请求的对象

与其他操作一样，检查返回的状态码允许你获取绑定到模型的操作所返回的任何验证消息

<?php

$phql = "
    DELETE
    FROM
        Invoices
    WHERE
        inv_cst_id > 10";

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

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

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

查询生成器

Phalcon\Mvc\Model\Query\Builder是一个非常方便的构建器，允许你以面向对象的方式来构建PHQL语句。大多数方法都返回构建器对象，允许你使用流畅的接口，并且足够灵活，使你可以根据需要添加条件，而无需创建复杂的if语句和字符串拼接来构造PHQL语句。

PHQL查询：

SELECT 
    * 
FROM 
    Invoices 
ORDER BY 
    inv_title

可以如下创建和执行：

<?php

use MyApp\Models\Invoices;

$invoices = $this
    ->modelsManager
    ->createBuilder()
    ->from(Invoices::class)
    ->orderBy('inv_title')
    ->getQuery()
    ->execute();

获取单行：

<?php

use MyApp\Models\Invoices;

$invoices = $this
    ->modelsManager
    ->createBuilder()
    ->from(Invoices::class)
    ->orderBy('inv_title')
    ->getQuery()
    ->getSingleResult();

参数

无论你是直接创建Phalcon\Mvc\Model\Query\Builder对象还是使用模型管理器的createBuilder方法，你都可以始终使用流畅的接口构建你的查询或在构造函数中传递包含参数的数组。数组的键包括：

参数 类型 描述
bind array 要绑定的数据数组
bindTypes array PDO参数类型
container 依赖注入 DI容器
columns array|string 要选择的列
conditions array|string 条件（where）
distinct string 去重列
for_update bool 是否加锁
group array 分组列
having string having列
joins array 用于连接的模型类
limit array|int 记录数量限制（如20或[20, 20])
models array 使用的模型类
offset int 偏移量
order array|string 排序列
shared_lock bool 是否加共享锁 
<?php

use PDO;
use Phalcon\Mvc\Model\Query\Builder;
use Phalcon\Di\FactoryDefault as Di;

/* DI is mandatory to use the Query Builder */
$di = new Di();

$params = [
    "container" => $di,
    "models"     => [
        Users::class,
    ],
    "columns"    => ["id", "name", "status"],
    "conditions" => [
        [
            "created > :min: AND created < :max:",
            [
                "min" => "2013-01-01",
                "max" => "2014-01-01",
            ],
            [
                "min" => PDO::PARAM_STR,
                "max" => PDO::PARAM_STR,
            ],
        ],
    ],
    // or "conditions" => "created > '2013-01-01' AND created < '2014-01-01'",
    "group"      => ["id", "name"],
    "having"     => "name = 'Kamil'",
    "order"      => ["name", "id"],
    "limit"      => 20,
    "offset"     => 20,
    // or "limit" => [20, 20],
];

$builder = new Builder($params);

获取方法

方法 描述
autoescape(string $identifier): string 自动转义标识符，但仅在需要时才进行转义。
getBindParams(): array 返回默认绑定参数
getBindTypes(): array 返回默认绑定类型
getColumns(): string|array 返回要查询的列
getDistinct(): bool 返回SELECT DISTINCT / SELECT ALL子句
getFrom(): string|array 返回查询使用的模型
getGroupBy(): array 返回GROUP BY子句
getHaving(): string 返回HAVING子句
getJoins(): array 返回JOIN查询的join部分
getLimit(): string|array 返回当前LIMIT子句
getModels(): string|array|null 返回查询涉及的模型
getOffset(): int 返回当前OFFSET子句
getOrderBy(): string|array 返回ORDER BY子句
getPhql(): string 返回生成的PHQL语句
getQuery(): QueryInterface 返回构建好的查询
getWhere(): string|array 返回查询的条件

方法

public function addFrom(
    string $model, 
    string $alias = null
): BuilderInterface
添加一个模型。第一个参数是模型，第二个参数是模型的别名。

<?php

$builder->addFrom(
    Customers::class
);

$builder->addFrom(
    Customers::class,
    "c"
);


public function andHaving(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
将一个条件追加到当前的HAVING条件子句追加一个条件表达式，并使用AND运算符。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->andHaving("SUM(Invoices.inv_total) > 1000");

$builder->andHaving(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);


public function andWhere(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
将一个条件追加到当前的WHERE条件子句追加一个条件表达式，并使用AND运算符。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->andWhere("SUM(Invoices.inv_total) > 1000");

$builder->andWhere(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);


public function betweenHaving(
    string $expr, 
    mixed $minimum, 
    mixed $maximum, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个BETWEEN条件增加到当前HAVING条件子句。该方法接受表达式、最小值和最大值，以及用于条件的运算符。BETWEEN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->betweenHaving(
    "SUM(Invoices.inv_total)",
    1000,
    5000
);


public function betweenWhere(
    string $expr, 
    mixed $minimum, 
    mixed $maximum, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个BETWEEN条件增加到当前WHERE条件子句。该方法接受表达式、最小值和最大值，以及用于条件的运算符。BETWEEN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->betweenWhere(
    "Invoices.inv_total",
    1000,
    5000
);


public function columns(mixed $columns): BuilderInterface
设置要查询的列。该方法接受一个string或者一个array数组。如果你指定了包含特定keys键的数组，则这些键将用作相关列的别名。

<?php

// SELECT inv_id, inv_title
$builder->columns("inv_id, inv_title");

// SELECT inv_id, inv_title
$builder->columns(
    [
        "inv_id",
        "inv_title",
    ]
);

// SELECT inv_cst_id, inv_total
$builder->columns(
    [
        "inv_cst_id",
        "inv_total" => "SUM(inv_total)",
    ]
);


public function distinct(mixed $distinct): BuilderInterface
设置SELECT DISTINCT / SELECT ALL标志

<?php

$builder->distinct("status");
$builder->distinct(null);


public function forUpdate(bool $forUpdate): BuilderInterface
设置一个FOR UPDATE子句

<?php

$builder->forUpdate(true);


public function from(mixed $models): BuilderInterface
设置查询的模型。该方法接受一个string或者一个array数组。如果你指定了包含特定keys键的数组，则这些键将用作相关模型的别名。

<?php

$builder->from(
    Invoices::class
);

$builder->from(
    [
        Invoices::class,
        Customers::class,
    ]
);

$builder->from(
    [
        'i' => Invoices::class,
        'c' => Customers::class,
    ]
);


public function groupBy(mixed $group): BuilderInterface
添加一个GROUP BY条件到构建器。

<?php

$builder->groupBy(
    [
        "Invoices.inv_cst_id",
    ]
);


public function having(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
设置表单的HAVING条件子句。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->having("SUM(Invoices.inv_total) > 1000");

$builder->having(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);


public function inHaving(
    string $expr, 
    array $values, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个IN条件增加到当前HAVING条件子句。该方法接受表达式、一个包含IN值的数组，以及用于条件的运算符。IN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->inHaving(
    "SUM(Invoices.inv_total)",
    [
        1000,
        5000,
    ]
);


public function innerJoin(
    string $model, 
    string $conditions = null, 
    string $alias = null
): BuilderInterface
添加一个INNER连接查询。第一个参数是模型。如果在各自的模型中正确设置了相关的关联关系，则连接条件会自动计算。不过，您可以使用第二个参数手动设置条件，而第三个参数（如果指定）则是别名。

<?php

$builder->innerJoin(
    Customers::class
);

$builder->innerJoin(
    Customers::class,
    "Invoices.inv_cst_id = Customers.cst_id"
);

$builder->innerJoin(
    Customers::class,
    "Invoices.inv_cst_id = c.cst_id",
    "c"
);


public function inWhere(
    string $expr, 
    array $values,  
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个IN条件增加到当前WHERE条件子句。该方法接受表达式、一个包含IN子句的值数组，以及用于条件的运算符。IN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->inWhere(
    "Invoices.inv_id",
    [1, 3, 5]
);

//Using OPERATOR_OR:
$builder->inWhere(
    "Invoices.inv_id",
    [1, 3, 5],
    \Phalcon\Mvc\Model\Query\BuilderInterface::OPERATOR_OR
);

public function join(
    string $model, 
    string $conditions = null, 
    string $alias = null, 
    string $type = null
): BuilderInterface

添加一个连接查询。第一个参数是模型。如果在各自的模型中正确设置了相关的关联关系，则连接条件会自动计算。不过，您可以使用第二个参数手动设置条件，而第三个参数（如果指定）则是别名。最后一个参数定义了连接的type类型。默认情况下，连接是INNERINNER JOIN。可接受的值包括：INNER, LEFT和RIGHT.

<?php

$builder->join(
    Customers::class
);

$builder->join(
    Customers::class,
    "Invoices.inv_cst_id = Customers.cst_id"
);

//If model `Invoices` has an alias, use it accordingly in the following two examples:
$builder->join(
    Customers::class,
    "Invoices.inv_cst_id = c.cst_id",
    "c"
);

$builder->join(
    Customers::class,
    "Invoices.inv_cst_id = c.cst_id",
    "c",
    "INNER"
);


public function leftJoin(
    string $model, 
    string $conditions = null, 
    string $alias = null
): BuilderInterface
添加一个LEFT连接查询。第一个参数是模型。如果在各自的模型中正确设置了相关的关联关系，则连接条件会自动计算。不过，您可以使用第二个参数手动设置条件，而第三个参数（如果指定）则是别名。

<?php

$builder->leftJoin(
    Customers::class
);

$builder->leftJoin(
    Customers::class,
    "Invoices.inv_cst_id = Customers.cst_id"
);

$builder->leftJoin(
    Customers::class,
    "Invoices.inv_cst_id = c.cst_id",
    "c"
);


public function limit(
    int $limit, 
    mixed $offset = null
): BuilderInterface
设置一个LIMIT子句，可选地以偏移量子句作为第二个参数

<?php

$builder->limit(100);
$builder->limit(100, 20);
$builder->limit("100", "20");


public function notBetweenHaving(
    string $expr, 
    mixed $minimum, 
    mixed $maximum, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个NOT BETWEEN条件增加到当前HAVING条件子句。该方法接受表达式、最小值和最大值，以及用于条件的运算符。NOT BETWEEN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->notBetweenHaving(
    "SUM(Invoices.inv_total)",
    1000,
    5000
);


public function notBetweenWhere(
    string $expr, 
    mixed $minimum, 
    mixed $maximum, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个NOT BETWEEN条件增加到当前WHERE条件子句。该方法接受表达式、最小值和最大值，以及用于条件的运算符。NOT BETWEEN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->notBetweenWhere(
    "Invoices.inv_total",
    1000,
    5000
);


public function notInHaving(
    string $expr, 
    array $values, 
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个NOT IN条件增加到当前HAVING条件子句。该方法接受表达式、一个包含IN值的数组，以及用于条件的运算符。NOT IN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->notInHaving(
    "SUM(Invoices.inv_total)",
    [
        1000,
        5000,
    ]
);


public function notInWhere(
    string $expr, 
    array $values,  
    string $operator = BuilderInterface::OPERATOR_AND
): BuilderInterface
添加一个NOT IN条件增加到当前WHERE条件子句。该方法接受表达式、一个包含IN子句的值数组，以及用于条件的运算符。NOT IN (OPERATOR_AND或OPERATOR_OR)

<?php

$builder->notInWhere(
    "Invoices.inv_id",
    [1, 3, 5]
);


public function offset(int $offset): BuilderInterface
设置一个OFFSET子句

<?php

$builder->offset(30);


public function orderBy(mixed $orderBy): BuilderInterface
设置一个ORDER BY条件子句。参数可以是字符串或数组。您还可以在每个列后面加上ASC或DESC后缀来定义排序方向。

<?php

$builder->orderBy("Invoices.inv_total");

$builder->orderBy(
    [
        "Invoices.inv_total",
    ]
);

$builder->orderBy(
    [
        "Invoices.inv_total DESC",
    ]
);


public function orHaving(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
将一个条件追加到当前的HAVING条件子句，使用OR运算符。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->orHaving("SUM(Invoices.inv_total) > 1000");

$builder->orHaving(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);


public function orWhere(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
将一个条件追加到当前的WHERE条件子句，使用OR运算符。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->orWhere("SUM(Invoices.inv_total) > 1000");

$builder->orWhere(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);


public function rightJoin(
    string $model, 
    string $conditions = null, 
    string $alias = null
): BuilderInterface
添加一个RIGHT连接查询。第一个参数是模型。如果在各自的模型中正确设置了相关的关联关系，则连接条件会自动计算。不过，您可以使用第二个参数手动设置条件，而第三个参数（如果指定）则是别名。

<?php

$builder->rightJoin(
    Customers::class
);

$builder->rightJoin(
    Customers::class,
    "Invoices.inv_cst_id = Customers.cst_id"
);

$builder->rightJoin(
    Customers::class,
    "Invoices.inv_cst_id = c.cst_id",
    "c"
);


public function setBindParams(
    array $bindParams, 
    bool $merge = false
): BuilderInterface
设置默认绑定参数。第一个参数是一个数组，其中键是绑定参数的名称或编号。第二个参数是一个布尔值，指示组件是否将提供的参数合并到现有的栈中。

<?php

$builder->setBindParams(
    [
        "sum" => 1000,
    ]
);

$builder->setBindParams(
    [
        "cst_id" => 10,
    ],
    true
);

$builder->where(
    "SUM(Invoices.inv_total) > :sum: AND inv_cst_id > :cst_id:",
    [
        "sum"    => PDO::PARAM_INT,
        "cst_id" => PDO::PARAM_INT,
    ]
);


public function setBindTypes(
    array bindTypes, 
    bool $merge = false
): BuilderInterface
设置默认绑定类型。第一个参数是一个数组，其中键是绑定参数的名称或编号。第二个参数是一个布尔值，指示组件是否将提供的参数合并到现有的栈中。绑定类型为PDO 常量.

<?php

$builder->setBindParams(
    [
        "sum" => 1000,
    ]
);

$builder->setBindParams(
    [
        "cst_id" => 10,
    ],
    true
);

$builder->setBindTypes(
    [
        "sum" => PDO::PARAM_INT,
    ]
);

$builder->setBindTypes(
    [
        "cst_id" => PDO::PARAM_INT,
    ],
    true
);

$builder->where(
    "SUM(Invoices.inv_total) > :sum: AND inv_cst_id > :cst_id:"
);


public function where(
    mixed $conditions, 
    array $bindParams = [], 
    array $bindTypes = []
): BuilderInterface
设置表单的WHERE条件子句。第一个参数是表达式。第二个参数是一个数组，其中键是绑定参数的名称。最后一个参数是一个定义每个参数绑定类型的数组。绑定类型为PDO 常量.

<?php

$builder->where("SUM(Invoices.inv_total) > 1000");

$builder->where(
    "SUM(Invoices.inv_total) > :sum:",
    [
        "sum" => 1000,
    ],
    [
        "sum" => PDO::PARAM_INT,
    ]
);

示例

<?php

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices
$builder->from(Invoices::class);

// SELECT 
//      Invoices*, 
//      Customers.* 
// FROM 
//      Invoices, 
//      Customers
$builder->from(
    [
        Invoices::class,
        Customers::class,
    ]
);

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices
$builder
    ->columns('*')
    ->from(Invoices::class)
;

// SELECT 
//      Invoices.inv_id 
// FROM 
//      Invoices
$builder
    ->columns('inv_id')
    ->from(Invoices::class)
;

// SELECT 
//      Invoices.inv_id, 
//      Invoices.inv_title 
// FROM 
//      Invoices
$builder
    ->columns(
        [
            'inv_id', 
            'inv_title',
        ]
    )
    ->from(Invoices::class)
;

// SELECT 
//      Invoices.inv_id, 
//      Invoices.title_alias 
// FROM 
//      Invoices
$builder
    ->columns(
        [
            'inv_id', 
            'title_alias' => 'inv_title',
        ]
    )
    ->from(Invoices::class)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      Invoices.inv_cst_id = 1
$builder
    ->from(Invoices::class)
    ->where("Invoices.inv_cst_id = 1")
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      Invoices.inv_id = 1
$builder
    ->from(Invoices::class)
    ->where(1)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      Invoices.inv_cst_id = 1
// AND 
//      Invoices.inv_total > 1000
$builder
    ->from(Invoices::class)
    ->where("inv_cst_id = 1")
    ->andWhere('inv_total > 1000')
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      Invoices.inv_cst_id = 1
// OR 
//      Invoices.inv_total > 1000
$builder
    ->from(Invoices::class)
    ->where("inv_cst_id = 1")
    ->orWhere('inv_total > 1000')
;


// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// GROUP BY 
//      Invoices.inv_cst_id
$builder
    ->from(Invoices::class)
    ->groupBy('Invoices.inv_cst_id')
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// GROUP BY 
//      Invoices.inv_cst_id,
//      Invoices.inv_status_flag
$builder
    ->from(Invoices::class)
    ->groupBy(
        [
            'Invoices.inv_cst_id',
            'Invoices.inv_status_flag',
        ]
    )
;

// SELECT 
//      Invoices.inv_title, 
//      SUM(Invoices.inv_total) AS total
// FROM 
//      Invoices 
// GROUP BY 
//      Invoices.inv_cst_id
$builder
    ->columns(
        [
            'Invoices.inv_title', 
            'total' => 'SUM(Invoices.inv_total)'
        ]
    )
    ->from(Invoices::class)
    ->groupBy('Invoices.inv_cst_id')
;

// SELECT 
//      Invoices.inv_title, 
//      SUM(Invoices.inv_total) AS total
// FROM 
//      Invoices 
// GROUP BY 
//      Invoices.inv_cst_id
// HAVING
//      Invoices.inv_total > 1000
$builder
    ->columns(
        [
            'Invoices.inv_title', 
            'total' => 'SUM(Invoices.inv_total)'
        ]
    )
    ->from(Invoices::class)
    ->groupBy('Invoices.inv_cst_id')
    ->having('SUM(Invoices.inv_total) > 1000')
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// JOIN 
//      Customers
$builder
    ->from(Invoices::class)
    ->join(Customers::class)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// JOIN 
//      Customers AS c
$builder
    ->from(Invoices::class)
    ->join(Customers::class, null, 'c')
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices AS i
// JOIN 
//      Customers AS c
// ON
//      i.inv_cst_id = c.cst_id
$builder
    ->from(Invoices::class, 'i')
    ->join(
        Customers::class, 
        'i.inv_cst_id = c.cst_id', 
        'c'
    )
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices AS i
// JOIN 
//      InvoicesXProducts AS x
// ON
//      i.inv_id = x.ixp_inv_id
// JOIN 
//      Products AS prd
// ON
//      x.ixp_prd_id = p.prd_id
$builder
    ->addFrom(Invoices::class, 'i')
    ->join(
        InvoicesXProducts::class, 
        'i.inv_id = x.ixp_inv_id', 
        'x'
    )
    ->join(
        Products::class, 
        'x.ixp_prd_id = p.prd_id', 
        'p'
    )
;

// SELECT 
//      Invoices.*, 
//      c.* 
// FROM 
//      Invoices, 
//      Customers AS c
$builder
    ->from(Invoices::class)
    ->addFrom(Customers::class, 'c')
;

// SELECT 
//      i.*, 
//      c.* 
// FROM 
//      Invoices AS i, 
//      Customers AS c
$builder
    ->from(
        [
            'i' => Invoices::class,
            'c' => Customers::class,
        ]
    )
;


// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// LIMIT 
//      10
$builder
    ->from(Invoices::class)
    ->limit(10)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// LIMIT 
//      10
// OFFSET
//      5
$builder
    ->from(Invoices::class)
    ->limit(10, 5)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      inv_id 
// BETWEEN 
//      1 
// AND 
//      100
$builder
    ->from(Invoices::class)
    ->betweenWhere('inv_id', 1, 100)
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      inv_id 
// IN 
//      (1, 2, 3)
$builder
    ->from(Invoices::class)
    ->inWhere(
        'inv_id', 
        [1, 2, 3]
    )
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      inv_id 
// NOT IN 
//      (1, 2, 3)
$builder
    ->from(Invoices::class)
    ->notInWhere(
        'inv_id', 
        [1, 2, 3]
    )
;

// SELECT 
//      Invoices.* 
// FROM 
//      Invoices 
// WHERE 
//      inv_title 
// LIKE 
//      '%ACME%';
$title = 'ACME';
$builder
    ->from(Invoices::class)
    ->where(
        'inv_title LIKE :title:', 
        [
            'title' => '%' . $title . '%',
        ]
    )
;

绑定参数

查询生成器中的绑定参数可以在构建查询时或执行查询时设置：

<?php

$invoices = $this
    ->modelsManager
    ->createBuilder()
    ->from(Invoices::class)
    ->where(
        'inv_cst_id = :cst_id:', 
        [
            'cst_id' => 1,
        ]
    )
    ->andWhere(
        'inv_total = :total:', 
        [
            'total' => 1000,
        ]
    )
    ->getQuery()
    ->execute();

$invoices = $this
    ->modelsManager
    ->createBuilder()
    ->from(Invoices::class)
    ->where('inv_cst_id = :cst_id:')
    ->andWhere('inv_total = :total:')
    ->getQuery()
    ->execute(
        [
            'cst_id' => 1,
            'total'  => 1000,
        ]
    )
;

在 PHQL 中禁用字面量

在 PHQL 中可以禁用字面量。这意味着你不能在 PHQL 中使用字符串、数字或布尔值。你必须改用绑定参数。

注意

禁用字面量可以提高数据库语句的安全性并减少 SQL 注入的可能性。

注意

此设置可以为所有模型全局设置。请参考相关文档了解如何操作以及其他设置。模型有关用法和附加设置，请参阅文档。

下面的查询可能会导致 SQL 注入：

<?php

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

如果$login被更改为' OR '' = '，生成的 PHQL 是：

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

这始终是true不论数据库中存储的登录信息是什么。如果禁用了字面量，在 PHQL 字符串中使用字符串、数字或布尔值会导致抛出异常，从而强制开发人员使用绑定参数。同样的查询可以更安全地编写如下：

<?php

$login  = 'admin';
$phql   = "SELECT * FROM Users WHERE login = :login:";
$result = $manager->executeQuery(
    $phql,
    [
        'login' => $login,
    ]
);

您可以通过以下方式禁止使用字面量：

<?php

use Phalcon\Mvc\Model;

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

无论是否禁用了字面量，您都可以（并且应该）使用绑定参数。

保留字

PHQL 在内部使用了一些保留字。如果您希望将其中任何一个用作属性或模型名称，则需要使用跨数据库转义分隔符对其进行转义。[和]:

<?php

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

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

分隔符会根据应用程序连接的数据库系统动态转换为有效的分隔符。

自定义方言

由于基于所选 RDBMS 的 SQL 方言存在差异，并非所有方法都受支持。但是，您可以扩展方言，以便使用您的 RDBMS 支持的其他功能。

在下面的示例中，我们使用了MATCH_AGAINST针对 MySQL 的方法。

<?php

use Phalcon\Db\Dialect\MySQL as Dialect;
use Phalcon\Db\Adapter\Pdo\MySQL as Connection;

$dialect = new Dialect();
$dialect->registerCustomFunction(
    'MATCH_AGAINST',
    function ($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            " MATCH (%s) AGAINST (%s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
         );
    }
);

$connection = new Connection(
    [
        "host"          => "localhost",
        "username"      => "root",
        "password"      => "secret",
        "dbname"        => "phalcon",
        "dialectClass"  => $dialect
    ]
);

现在您可以在 PHQL 中使用此函数，并且它会在内部使用自定义函数转换为正确的 SQL：

$phql = "SELECT *
         FROM Invoices
         WHERE MATCH_AGAINST(inv_title, :pattern:)";

$invoices = $modelsManager
    ->executeQuery(
        $phql, 
        [
            'pattern' => $pattern
        ]
    )
;

另一个展示GROUP_CONCAT:

<?php

use Phalcon\Db\Dialect\MySQL as Dialect;
use Phalcon\Db\Adapter\Pdo\MySQL as Connection;

$dialect = new Dialect();
$dialect->registerCustomFunction(
    'GROUPCONCAT',
    function ($dialect, $expression) {
        $arguments = $expression['arguments'];
        if (true !== empty($arguments[2])) {
            return sprintf(
                " GROUP_CONCAT(DISTINCT %s ORDER BY %s SEPARATOR %s)",
                $dialect->getSqlExpression($arguments[0]),
                $dialect->getSqlExpression($arguments[1]),
                $dialect->getSqlExpression($arguments[2]),
            );
        } elseif (true !== empty($arguments[1])) {
            return sprintf(
                " GROUP_CONCAT(%s SEPARATOR %s)",
                $dialect->getSqlExpression($arguments[0]),
                $dialect->getSqlExpression($arguments[1])
            );
        } else {
            return sprintf(
                " GROUP_CONCAT(%s)",
                $dialect->getSqlExpression($arguments[0])
            );
        }
    }
);

$connection = new Connection(
    [
        "host"          => "localhost",
        "username"      => "root",
        "password"      => "secret",
        "dbname"        => "phalcon",
        "dialectClass"  => $dialect
    ]
);

现在您可以在 PHQL 中使用此函数，并且它会在内部使用自定义函数转换为正确的 SQL：

$phql = "SELECT GROUPCONCAT(inv_title, inv_title, :separator:)
         FROM Invoices";

$invoices = $modelsManager
    ->executeQuery(
        $phql, 
        [
            'separator' => ", "
        ]
    )
;

上述代码将创建一个GROUP_CONCAT根据传递给方法的参数而定。如果传递三个参数，我们将得到一个GROUP_CONCAT结合一个DISTINCT, ORDER BY和SEPARATOR，如果传递两个参数，我们将得到一个GROUP_CONCAT的文件SEPARATOR，如果只传递一个参数，则仅得到一个GROUP_CONCAT

缓存

PHQL 查询可以被缓存。您还可以查看 [模型缓存][db-models-cache] 文档以获取更多信息。

<?php

$phql  = 'SELECT * FROM Customers WHERE cst_id = :cst_id:';
$query = $this
    ->modelsManager
    ->createQuery($phql)
;

$query->cache(
    [
        'key'      => 'customers-1',
        'lifetime' => 300,
    ]
);

$invoice = $query->execute(
    [
        'cst_id' => 1,
    ]
);

生命周期

作为一种高级语言，PHQL 让开发者能够根据自身需求个性化和定制不同方面。以下是每次执行 PHQL 语句的生命周期：

  • PHQL 被解析并转换为中间表示（IR），该表示独立于数据库系统实现的 SQL
  • IR 根据与模型关联的数据库系统转换为有效的 SQL
  • PHQL 语句仅被解析一次，并缓存在内存中。同一语句的后续执行会稍微快一些

原始 SQL

数据库系统可能提供 PHQL 不支持的特定 SQL 扩展，在这种情况下，使用原生 SQL 是合适的：

<?php

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

class Invoices extends Model
{
    public static function findByCreateInterval()
    {
        $sql     = 'SELECT * FROM Invoices WHERE inv_id > 1';
        $invoice = new Invoices();

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

如果您的应用程序中经常使用原生 SQL 查询，可以向模型中添加一个通用方法：

<?php

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

class Invoices extends Model
{
    public static function findByRawSql(
        string $conditions, 
        array $params = null
    ) {
        $sql     = 'SELECT * FROM Invoices WHERE ' . $conditions;
        $invoice = new Invoices();

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

上述findByRawSql可以按如下方式使用：

<?php

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

排查问题

使用 PHQL 时需要注意的一些事项：

  • 类名是区分大小写的，如果类名未按照与创建时相同的大小写定义，这可能导致在像 Linux 这样文件系统区分大小写的操作系统中出现意外行为。
  • 必须在连接中定义正确的字符集以成功绑定参数。
  • 别名类不会被替换为完整命名空间的类，因为这种情况仅发生在 PHP 代码中，而不是在字符串内部。
  • 如果启用了列重命名，请避免使用与要重命名的列同名的列别名，这可能会使查询解析器感到困惑。
无噪 Logo
无噪文档
↗
25 年 6 月翻译
版本号 5.9
文档源↗