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

Phalcon 查询语言（以下简称 PHQL）是一种面向对象的高级 SQL 方言，与标准化的 SQL 相似。Phalcon 内部实现了一个解析器（和 SQLite 中相似的解析器）语句解析成符合 RDBMS 的语法。

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

• 绑定参数，使你的代码更安全
• 使用预处理语句
• 忽略语句中的所有注释
• 只允许数据操作语句，避免误修改、删除表或数据库
• 允许在模型中处理表中字段

标识名不能添加引号（反单引号、单引号或双引号）。

范例（Usage Example）

通过下面的列子让我们更清除的了解 PHQL 是如何工作的。我们有两个模型 “Cars” 和 “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 查询（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();

在控制器或者视图中，利用注入服务 models 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(
    array(
        '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:", array(
    'name' => 'Audi'
));

选取记录（Selecting Records）

As the familiar SQL, PHQL allows querying of records using the SELECT statement we know, except that instead of specifying tables, we use the models classes:

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

Classes in namespaces are also allowed:

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

Most of the SQL standard is supported by PHQL, even nonstandard directives such as 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）

Depending on the type of columns we query, the result type will vary. If you retrieve a single whole object, then the object returned isa Phalcon\Mvc\Model\Resultset\Simple. This kind of resultset is a set of complete model objects:

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

This is exactly the same as:

<?php
 
$cars = Cars::find(
    array(
        "order" => "name"
    )
);
 
foreach ($cars as $car) {
    echo "Name: ", $car->name, "\n";
}

Complete objects can be modified and re-saved in the database because they represent a complete record of the associated table. There areother types of queries that do not return complete objects, for example:

<?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 isstill a resultset of type Phalcon\Mvc\Model\Resultset\Simple. However, each element is a standardobject that only contain the two columns that were requested.

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

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:

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

It’s easy to request records from multiple models using PHQL. Most kinds of Joins are supported. As we definedrelationships 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);

It is also possible to manually set the conditions of the 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:

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

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:

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

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

<?php
 
$phql = "SELECT * FROM Cars WHERE Cars.name = :name:";
$cars = $manager->executeQuery($phql, array("name" => 'Lamborghini Espada'));
 
$phql = "SELECT * FROM Cars WHERE Cars.name = ?0";
$cars = $manager->executeQuery($phql, array(0 => 'Lamborghini Espada'));

插入数据（Inserting Data）

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

<?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,
    array(
        '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 definedin the model are executed as if we created individual objects manually. Let’s add a business ruleon the model cars. A car cannot cost less than $ 10,000:

<?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 successfulbecause the price does not meet the business rule that we implemented. By checking thestatus 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 toupdate records is UPDATE. When a record is updated the events related to the update operationwill be executed for each row.

<?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,
    array(
        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:

<?php
 
$phql   = "UPDATE Cars SET price = 15000.00 WHERE id > 101";
$result = $manager->executeQuery($phql);
if ($result->success() == false) {
    foreach ($result->getMessages() as $message) {
        echo $message->getMessage();
    }
}

is somewhat equivalent to:

<?php
 
$messages = null;
 
$process  = function () use (&$messages) {
    foreach (Cars::find("id > 101") 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,
    array(
        'initial' => 1,
        'final'   => 100
    )
);

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

// Deleting multiple rows
$phql = "DELETE FROM Cars WHERE id > 100";
$result = $manager->executeQuery($phql);
if ($result->success() == false) {
    foreach ($result->getMessages() 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:

选取查询构建器（Select Query Builder）

<?php
 
// Create a new Query Builder for Select
$robots = $this->modelsManager->createBuilder();
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_SELECT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createSelectBuilder();
 
// Getting a whole set
$builder->from('Robots')
    ->join('RobotsParts')
    ->orderBy('Robots.name')
    ->getQuery()
    ->execute();
 
// Getting the first row
$builder->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(
    array(
        '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(array('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(array('Robots.name', 'Robots.id'));
 
// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name';
$builder->columns(array('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(array('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(array('r' => 'Robots'))
        ->addFrom('Parts', 'p');
 
// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(array('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', array(1, 2, 3));
 
// 'SELECT Robots.* FROM Robots WHERE id NOT IN (1, 2, 3)';
$builder->from('Robots')
        ->notInWhere('id', array(1, 2, 3));
 
// 'SELECT Robots.* FROM Robots WHERE name LIKE '%Art%';
$builder->from('Robots')
        ->where('name LIKE :name:', array('name' => '%' . $name . '%'));
 
// 'SELECT r.* FROM Store\Robots WHERE r.name LIKE '%Art%';
$builder->from(['r' => 'Store\Robots'])
        ->where('r.name LIKE :name:', array('name' => '%' . $name . '%'));

插入查询构建器（Insert Query Builder）

<?php
 
// Create a new Query Builder for Insert
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_INSERT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_INSERT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createInsertBuilder();
 
// Insert two records
$builder->table('Robots')
    ->columns(array('name'))
    ->values(array(array('name' => 'Google'), array('name' => 'Baidu')))
    ->getQuery()
    ->execute();

更新查询构建器（Update Query Builder）

<?php
 
// Create a new Query Builder for Update
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_UPDATE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_UPDATE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createUpdateBuilder();
 
// Update records
$builder->table('Robots')
    ->set(array('name' => 'Google'))
    ->getQuery()
    ->execute();

删除查询构建器（Delete Query Builder）

<?php
 
// Create a new Query Builder for Delete
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_DELETE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_DELETE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createDeleteBuilder();
 
// Delete records
$builder->table('Robots')
    ->where('name = "Peter"')
    ->orderBy('Robots.id')
    ->limit(20)
    ->getQuery()
    ->execute();

绑定参数（Bound Parameters）

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

<?php
 
// Passing parameters in the query construction
$robots = $this->modelsManager->createBuilder()
    ->from('Robots')
    ->where('name = :name:', array('name' => $name))
    ->andWhere('type = :type:', array('type' => $type))
    ->getQuery()
    ->execute();
 
// Passing parameters in query execution
$robots = $this->modelsManager->createBuilder()
    ->from('Robots')
    ->where('name = :name:')
    ->andWhere('type = :type:')
    ->getQuery()
    ->execute(array('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 stringswill be disallowed. If PHQL statements are created embedding external data on them, this could open the applicationto potential SQL injections:

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

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 exceptionwill be thrown forcing the developer to use bound parameters. The same query can be written in asecure way like this:

<?php
 
$phql   = "SELECT Robots.* FROM Robots WHERE Robots.name = :name:";
$result = $manager->executeQuery($phql, array('name' => $name));

You can disallow literals in the following way:

<?php
 
use Phalcon\Mvc\Model;
 
Model::setup(
    array(
        'phqlLiterals' => false
    )
);

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

转义保留字（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 thosewords using the cross-database escaping delimiters ‘[‘ 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

使用原生 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:

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

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

<?php
 
$robots = Robots::findByRawSql('id > ?', array(10));

实现读写分离（Reading and Writing Separation）

当查询器中实现此方法，将会替换 模型 中实现的方法，实现读写分离代码如下：

<?php
 
class MyQuery extends Phalcon\Mvc\Model\Query
{
    /**
     * 动态选择读数据库连接
     *
     * @param array $data
     */
    public function selectReadConnection($data)
    {
        $intermediate = $data[0];
        $bindParams = $data[1];
        $bindTypes = $data[2];
        return $this->getDI()->get('readDB');
    }
 
    /**
     * 动态选择写数据库连接
     *
     * @param array $intermediate
     * @param array $bindParams
     * @param array $bindTypes
     */
    public function selectWriteConnection($data)
    {
        $intermediate = $data[0];
        $bindParams = $data[1];
        $bindTypes = $data[2];
        return $this->getDI()->get('writeDB');
    }
}
 
$di->set('modelsQuery', 'MyQuery');

分库（Split Database）

根据当前查询条件来实现，实现水平切分的功能：

<?php
 
$di->modelsQuery->attachEvent('selectReadConnection', function($event, $data) {
    $intermediate = $data[0];
    $bindParams = $data[1];
    $bindTypes = $data[2];
 
    // Check if there is a 'where' clause in the select
    if (isset($intermediate['where'])) {
 
        $conditions = $intermediate['where'];
 
        // Choose the possible shard according to the conditions
        if ($conditions['left']['name'] == 'id') {
                $id = $conditions['right']['value'];
 
                if ($id > 0 && $id < 10000) {
                    return $this->getDI()->get('dbShard1');
                }
 
                if ($id > 10000) {
                    return $this->getDI()->get('dbShard2');
                }
        }
    }
 
    // Use a default shard
    return $this->getDI()->get('dbShard0');
});
 
// Or
class MyQuery extends Phalcon\Mvc\Model\Query
{
    /**
     * 动态选择读数据库连接
     *
     * @param array $intermediate
     * @param array $bindParams
     * @param array $bindTypes
     */
    public function selectReadConnection($event, $data)
    {
        $intermediate = $data[0];
        $bindParams = $data[1];
        $bindTypes = $data[2];
 
        // Check if there is a 'where' clause in the select
        if (isset($intermediate['where'])) {
 
            $conditions = $intermediate['where'];
 
            // Choose the possible shard according to the conditions
            if ($conditions['left']['name'] == 'id') {
                $id = $conditions['right']['value'];
 
                if ($id > 0 && $id < 10000) {
                    return $this->getDI()->get('dbShard1');
                }
 
                if ($id > 10000) {
                    return $this->getDI()->get('dbShard2');
                }
            }
        }
 
        // Use a default shard
        return $this->getDI()->get('dbShard0');
    }
}
 
$di->set('modelsQuery', 'MyQuery');

PHQL 事件（PHQL Events）

如果需要的话 Phalcon\MvcModelQuery 可以发送事件到 EventsManager 。支持如下的事件：

PHQL 事件（PHQL Events）

注意事项（Troubleshooting）

在使用 PHQL 我们需要注意一些事项:

• 类名是区分大小写的，如果 PHQL 使用的类名和真实的类名不一致，这在一些操作系统中可能会导致意外行为，比如 Linux 之类的区分大小写的文件系统。
• 必须在定义数据库连接时，设置正确的字符集参数。
• 在 PHQL 中，类的别名不能取代带完整命名空间的类，因为这只是字符串。
• 如果启用了列重命名，则避免使用与重命名的列同名的列别名，这可能会混淆查询解析器。

原文: http://www.myleftstudio.com/reference/phql.html