# Phalcon 查询语言（Phalcon Query Language (PHQL)）
# Phalcon 查询语言（Phalcon Query Language (PHQL)）
Phalcon Query Language, PhalconQL or simply PHQL is a high-level, object-oriented SQL dialect that allows to write queries using astandardized SQL-like language. PHQL is implemented as a parser (written in C) that translates syntax in that of the target RDBMS.
To achieve the highest performance possible, Phalcon provides a parser that uses the same technology as [SQLite](http://en.wikipedia.org/wiki/Lemon_Parser_Generator). This technologyprovides a small in-memory parser with a very low memory footprint that is also thread-safe.
The parser first checks the syntax of the pass PHQL statement, then builds an intermediate representation of the statement andfinally it converts it to the respective SQL dialect of the target RDBMS.
In PHQL, we've implemented a set of features to make your access to databases more secure:
- Bound parameters are part of the PHQL language helping you to secure your code
- PHQL only allows one SQL statement to be executed per call preventing injections
- PHQL ignores all SQL comments which are often used in SQL injections
- PHQL only allows data manipulation statements, avoiding altering or dropping tables/databases by mistake or externally without authorization
- PHQL implements a high-level abstraction allowing you to handle tables as models and fields as class attributes
### 范例（Usage Example）
To better explain how PHQL works consider the following example. We have two models “Cars” and “Brands”:
```
<pre class="calibre14">```
<?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');
}
}
```
```
And every Car has a Brand, so a Brand has many Cars:
```
<pre class="calibre14">```
<?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 queries can be created just by instantiating the class [*Phalcon\\Mvc\\Model\\Query*](#):
```
<pre class="calibre14">```
<?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();
```
```
From a controller or a view, it's easy to create/execute them using an injected [*models manager*](#):
```
<pre class="calibre14">```
<?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'
));
```
```
Or simply execute it:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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 as LIMIT:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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 resulset 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 we call them scalars. PHQL allows you to query all types of scalars: fields, functions, literals, expressions, etc..:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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);
```
```
Also is possible set manually the conditions of the JOIN:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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($sql,
array(
'name' => 'Lamborghini Espada',
'brand_id' => 7,
'year' => 1969,
'style' => 'Grand Tourer',
)
);
```
```
Phalcon not just 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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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.
```
<pre class="calibre14">```
<?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](http://process.In) summary, the following code:
```
<pre class="calibre14">```
<?php
$phql = "UPDATE Cars SET price = 15000.00 WHERE id > 101";
$success = $manager->executeQuery($phql);
```
```
is somewhat equivalent to:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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.
### 使用查询构建器创建查询（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:
```
<pre class="calibre14">```
<?php
// Getting a whole set
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->execute();
// Getting the first row
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->getSingleResult();
```
```
That is the same as:
```
<pre class="calibre14">```
<?php
$phql = "SELECT Robots.*
FROM Robots JOIN RobotsParts p
ORDER BY Robots.name LIMIT 20";
$result = $manager->executeQuery($phql);
```
```
More examples of the builder:
```
<pre class="calibre14">```
<?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 . '%'));
```
```
### 绑定参数（Bound Parameters）
Bound parameters in the query builder can be set as the query is constructed or past all at once when executing:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?php
"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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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 ‘\]':
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?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:
```
<pre class="calibre14">```
<?php
$robots = Robots::findByRawSql('id > ?', array(10));
```
```
### 注意事项（Troubleshooting）
Some things to keep in mind when using PHQL:
- Classes are case-sensitive, if a class is not defined with the same name as it was created this could lead to an unexpected behavior in operating systems with case-sensitive file systems such as Linux.
- Correct charset must be defined in the connection to bind parameters with success
- Aliased classes aren't replaced by full namespaced classes since this only occurs in PHP code and not inside strings
- If column renaming is enabled avoid using column aliases with the same name as columns to be renamed, this may confuse the query resolver
|
- [索引](# "总目录")
- [下一页](# "缓存对象关系映射（Caching in the ORM）") |
- [上一页](# "事务管理（Model Transactions）") |
- API参考
- API列表
- Abstract class Phalcon\Acl
- Abstract class Phalcon\Acl\Adapter
- Class Phalcon\Acl\Adapter\Memory
- Interface Phalcon\Acl\AdapterInterface
- Class Phalcon\Acl\Exception
- Class Phalcon\Acl\Resource
- Interface Phalcon\Acl\ResourceInterface
- Class Phalcon\Acl\Role
- Interface Phalcon\Acl\RoleInterface
- Class Phalcon\Annotations\Annotation
- Abstract class Phalcon\Annotations\Adapter
- Interface Phalcon\Annotations\AdapterInterface
- Class Phalcon\Annotations\Collection
- Class Phalcon\Annotations\Exception
- Class Phalcon\Annotations\Reader
- Interface Phalcon\Annotations\ReaderInterface
- Class Phalcon\Annotations\Reflection
- Class Phalcon\Assets\Collection
- Class Phalcon\Assets\Exception
- Interface Phalcon\Assets\FilterInterface
- Class Phalcon\Assets\Filters\Cssmin
- Class Phalcon\Assets\Filters\Jsmin
- Class Phalcon\Assets\Filters\None
- Class Phalcon\Assets\Inline
- Class Phalcon\Assets\Inline\Css
- Class Phalcon\Assets\Inline\Js
- Class Phalcon\Assets\Manager
- Class Phalcon\Assets\Resource
- Class Phalcon\Assets\Resource\Css
- Class Phalcon\Assets\Resource\Js
- Abstract class Phalcon\Cache\Backend
- Class Phalcon\Cache\Backend\Apc
- Class Phalcon\Cache\Backend\File
- Class Phalcon\Cache\Backend\Libmemcached
- Class Phalcon\Cache\Backend\Memcache
- Class Phalcon\Cache\Backend\Memory
- Class Phalcon\Cache\Backend\Mongo
- Class Phalcon\Cache\Backend\Redis
- Class Phalcon\Cache\Backend\Xcache
- Interface Phalcon\Cache\BackendInterface
- Class Phalcon\Cache\Exception
- Class Phalcon\Cache\Frontend\Base64
- Class Phalcon\Cache\Frontend\Data
- Class Phalcon\Cache\Frontend\Igbinary
- Class Phalcon\Cache\Frontend\Json
- Class Phalcon\Cache\Frontend\None
- Class Phalcon\Cache\Frontend\Output
- Interface Phalcon\Cache\FrontendInterface
- Class Phalcon\Cache\Multiple
- Class Phalcon\Cli\Router\Route
- Class Phalcon\Config
- Class Phalcon\Config\Adapter\Ini
- Class Phalcon\Config\Adapter\Json
- Class Phalcon\Config\Adapter\Php
- Class Phalcon\Config\Adapter\Yaml
- Class Phalcon\Config\Exception
- Class Phalcon\Crypt
- Class Phalcon\Crypt\Exception
- Interface Phalcon\CryptInterface
- Abstract class Phalcon\Db
- Abstract class Phalcon\Db\Adapter
- Interface Phalcon\Db\AdapterInterface
- Class Phalcon\Db\Column
- Interface Phalcon\Db\ColumnInterface
- Abstract class Phalcon\Db\Dialect
- Interface Phalcon\Db\DialectInterface
- Class Phalcon\Db\Exception
- Class Phalcon\Db\Index
- Interface Phalcon\Db\IndexInterface
- Class Phalcon\Db\Profiler
- Class Phalcon\Db\RawValue
- Class Phalcon\Db\Reference
- Interface Phalcon\Db\ReferenceInterface
- Class Phalcon\Db\Result\Pdo
- Interface Phalcon\Db\ResultInterface
- Class Phalcon\Debug
- Class Phalcon\Debug\Dump
- Class Phalcon\Debug\Exception
- Interface Phalcon\DiInterface
- Abstract class Phalcon\Dispatcher
- Interface Phalcon\DispatcherInterface
- Class Phalcon\Escaper
- Class Phalcon\Escaper\Exception
- Interface Phalcon\EscaperInterface
- Class Phalcon\Events\Event
- Interface Phalcon\Events\EventsAwareInterface
- Class Phalcon\Events\Exception
- Class Phalcon\Events\Manager
- Interface Phalcon\Events\ManagerInterface
- Class Phalcon\Exception
- Class Phalcon\Filter
- Class Phalcon\Filter\Exception
- Interface Phalcon\Filter\UserFilterInterface
- Interface Phalcon\FilterInterface
- Abstract class Phalcon\Flash
- Class Phalcon\Flash\Direct
- Class Phalcon\Flash\Exception
- Class Phalcon\Flash\Session
- Interface Phalcon\FlashInterface
- Class Phalcon\Forms\Form
- Abstract class Phalcon\Forms\Element
- Class Phalcon\Forms\Exception
- Class Phalcon\Forms\Manager
- Class Phalcon\Http\Cookie
- Class Phalcon\Http\Cookie\Exception
- Class Phalcon\Http\Request
- Class Phalcon\Http\Request\Exception
- Class Phalcon\Http\Request\File
- Interface Phalcon\Http\Request\FileInterface
- Interface Phalcon\Http\RequestInterface
- Class Phalcon\Http\Response
- Class Phalcon\Http\Response\Cookies
- Interface Phalcon\Http\Response\CookiesInterface
- Class Phalcon\Http\Response\Exception
- Class Phalcon\Http\Response\Headers
- Interface Phalcon\Http\Response\HeadersInterface
- Interface Phalcon\Http\ResponseInterface
- Class Phalcon\Image
- Abstract class Phalcon\Image\Adapter
- Class Phalcon\Image\Adapter\Imagick
- Interface Phalcon\Image\AdapterInterface
- Class Phalcon\Image\Exception
- Class Phalcon\Kernel
- Class Phalcon\Loader
- Class Phalcon\Loader\Exception
- Abstract class Phalcon\Logger
- Abstract class Phalcon\Logger\Adapter
- Class Phalcon\Logger\Adapter\File
- Class Phalcon\Logger\Adapter\Firephp
- Class Phalcon\Logger\Adapter\Stream
- Class Phalcon\Logger\Adapter\Syslog
- Interface Phalcon\Logger\AdapterInterface
- Class Phalcon\Logger\Exception
- Abstract class Phalcon\Logger\Formatter
- Interface Phalcon\Logger\FormatterInterface
- Class Phalcon\Logger\Item
- Class Phalcon\Logger\Multiple
- Class Phalcon\Mvc\Application
- Class Phalcon\Mvc\Application\Exception
- Abstract class Phalcon\Mvc\Collection
- Abstract class Phalcon\Mvc\Collection\Behavior
- Class Phalcon\Mvc\Collection\Behavior\SoftDelete
- Class Phalcon\Mvc\Collection\Behavior\Timestampable
- Interface Phalcon\Mvc\Collection\BehaviorInterface
- Class Phalcon\Mvc\Collection\Document
- Class Phalcon\Mvc\Collection\Exception
- Class Phalcon\Mvc\Collection\Manager
- Interface Phalcon\Mvc\Collection\ManagerInterface
- Interface Phalcon\Mvc\CollectionInterface
- Abstract class Phalcon\Mvc\Controller
- Interface Phalcon\Mvc\ControllerInterface
- Class Phalcon\Mvc\Dispatcher
- Class Phalcon\Mvc\Dispatcher\Exception
- Interface Phalcon\Mvc\DispatcherInterface
- Interface Phalcon\Mvc\EntityInterface
- Class Phalcon\Mvc\Micro
- Class Phalcon\Mvc\Micro\Collection
- Interface Phalcon\Mvc\Micro\CollectionInterface
- Class Phalcon\Mvc\Micro\Exception
- Class Phalcon\Mvc\Micro\LazyLoader
- Interface Phalcon\Mvc\Micro\MiddlewareInterface
- Abstract class Phalcon\Mvc\Model
- Abstract class Phalcon\Mvc\Model\Behavior
- Class Phalcon\Mvc\Model\Criteria
- Interface Phalcon\Mvc\Model\CriteriaInterface
- Class Phalcon\Mvc\Model\Exception
- Class Phalcon\Mvc\Model\Manager
- Interface Phalcon\Mvc\Model\ManagerInterface
- Class Phalcon\Mvc\Model\Message
- Interface Phalcon\Mvc\Model\MessageInterface
- Abstract class Phalcon\Mvc\Model\MetaData
- Interface Phalcon\Mvc\Model\MetaDataInterface
- Class Phalcon\Mvc\Model\Query
- Interface Phalcon\Mvc\Model\QueryInterface
- Class Phalcon\Mvc\Model\Relation
- Interface Phalcon\Mvc\Model\RelationInterface
- Interface Phalcon\Mvc\Model\ResultInterface
- Abstract class Phalcon\Mvc\Model\Resultset
- Abstract class Phalcon\Mvc\Model\Validator
- Interface Phalcon\Mvc\Model\ResultsetInterface
- Class Phalcon\Mvc\Model\Row
- Class Phalcon\Mvc\Model\Transaction
- Interface Phalcon\Mvc\Model\TransactionInterface
- Class Phalcon\Mvc\Model\ValidationFailed
- Interface Phalcon\Mvc\ModelInterface
- Interface Phalcon\Mvc\ModuleDefinitionInterface
- Class Phalcon\Mvc\Router
- Class Phalcon\Mvc\Router\Annotations
- Class Phalcon\Mvc\Router\Exception
- Class Phalcon\Mvc\Router\Group
- Interface Phalcon\Mvc\Router\GroupInterface
- Class Phalcon\Mvc\Router\Route
- Interface Phalcon\Mvc\Router\RouteInterface
- Interface Phalcon\Mvc\RouterInterface
- Class Phalcon\Mvc\Url
- Class Phalcon\Mvc\Url\Exception
- Interface Phalcon\Mvc\UrlInterface
- Class Phalcon\Mvc\User\Component
- Class Phalcon\Mvc\User\Module
- Class Phalcon\Mvc\User\Plugin
- Class Phalcon\Mvc\View
- Abstract class Phalcon\Mvc\View\Engine
- Interface Phalcon\Mvc\View\EngineInterface
- Class Phalcon\Mvc\View\Exception
- Class Phalcon\Mvc\View\Simple
- Interface Phalcon\Mvc\ViewBaseInterface
- Interface Phalcon\Mvc\ViewInterface
- Abstract class Phalcon\Paginator\Adapter
- Class Phalcon\Paginator\Adapter\Model
- Class Phalcon\Paginator\Adapter\NativeArray
- Class Phalcon\Paginator\Adapter\QueryBuilder
- Interface Phalcon\Paginator\AdapterInterface
- Class Phalcon\Paginator\Exception
- Class Phalcon\Queue\Beanstalk
- Class Phalcon\Queue\Beanstalk\Job
- Final class Phalcon\Registry
- Class Phalcon\Security
- Class Phalcon\Security\Exception
- Abstract class Phalcon\Session
- Abstract class Phalcon\Session\Adapter
- Interface Phalcon\Session\AdapterInterface
- Class Phalcon\Session\Bag
- Interface Phalcon\Session\BagInterface
- Class Phalcon\Session\Exception
- Class Phalcon\Tag
- Class Phalcon\Tag\Exception
- Abstract class Phalcon\Tag\Select
- Abstract class Phalcon\Text
- Abstract class Phalcon\Translate
- Abstract class Phalcon\Translate\Adapter
- Class Phalcon\Translate\Adapter\Csv
- Class Phalcon\Translate\Adapter\Gettext
- Class Phalcon\Translate\Adapter\NativeArray
- Interface Phalcon\Translate\AdapterInterface
- Class Phalcon\Translate\Exception
- Class Phalcon\Validation
- Class Phalcon\Validation\Exception
- Class Phalcon\Validation\Message
- Class Phalcon\Validation\Message\Group
- Interface Phalcon\Validation\MessageInterface
- Abstract class Phalcon\Validation\Validator
- Class Phalcon\Validation\Validator\Alnum
- Class Phalcon\Validation\Validator\Alpha
- Class Phalcon\Validation\Validator\Between
- Class Phalcon\Validation\Validator\Confirmation
- Class Phalcon\Validation\Validator\Digit
- Class Phalcon\Validation\Validator\Email
- Class Phalcon\Validation\Validator\ExclusionIn
- Class Phalcon\Validation\Validator\File
- Class Phalcon\Validation\Validator\Identical
- Class Phalcon\Validation\Validator\InclusionIn
- Class Phalcon\Validation\Validator\Numericality
- Class Phalcon\Validation\Validator\PresenceOf
- Class Phalcon\Validation\Validator\Regex
- Class Phalcon\Validation\Validator\StringLength
- Class Phalcon\Validation\Validator\Uniqueness
- Class Phalcon\Validation\Validator\Url
- Interface Phalcon\Validation\ValidatorInterface
- Class Phalcon\Version
- 参考手册
- 安装（Installation）
- 教程 1：让我们通过例子来学习（Tutorial 1: Let’s learn by example）
- 教程 2：Introducing INVO（Tutorial 2: Introducing INVO）
- 教程 3: Securing INVO
- 教程 4: Using CRUDs
- 教程 5: Customizing INVO
- 教程 6: Vkuró
- 教程 7：创建简单的 REST API（Tutorial 7: Creating a Simple REST API）
- 示例列表（List of examples）
- 依赖注入与服务定位器（Dependency Injection/Service Location）
- MVC 架构（The MVC Architecture）
- 使用控制器（Using Controllers）
- 使用模型（Working with Models）
- 模型元数据（Models Meta-Data）
- 事务管理（Model Transactions）
- Phalcon 查询语言（Phalcon Query Language (PHQL)）
- 缓存对象关系映射（Caching in the ORM）
- 对象文档映射 ODM (Object-Document Mapper)
- 使用视图（Using Views）
- 视图助手（View Helpers）
- 资源文件管理（Assets Management）
- Volt 模版引擎（Volt: Template Engine）
- MVC 应用（MVC Applications）
- 路由（Routing）
- 调度控制器（Dispatching Controllers）
- 微应用（Micro Applications）
- 使用命名空间（Working with Namespaces）
- 事件管理器（Events Manager）
- Request Environment
- 返回响应（Returning Responses）
- Cookie 管理（Cookies Management）
- 生成 URL 和 路径（Generating URLs and Paths）
- 闪存消息（Flashing Messages）
- 使用 Session 存储数据（Storing data in Session）
- 过滤与清理（Filtering and Sanitizing）
- 上下文编码（Contextual Escaping）
- 验证（Validation）
- 表单（Forms）
- 读取配置（Reading Configurations）
- 分页（Pagination）
- 使用缓存提高性能（Improving Performance with Cache）
- 安全（Security）
- Encryption/Decryption
- 访问控制列表 ACL（Access Control Lists ACL）
- 多语言支持（Multi-lingual Support）
- Universal Class Loader
- 日志记录（Logging）
- 注释解析器（Annotations Parser）
- 命令行应用（Command Line Applications）
- 队列（Queueing）
- 数据库抽象层（Database Abstraction Layer）
- 国际化（Internationalization）
- 数据库迁移（Database Migrations）
- 调试应用程序（Debugging Applications）
- Phalcon 开发工具（Phalcon Developer Tools）
- 提高性能：下一步该做什么？（Increasing Performance: What’s next?）
- 单元测试（Unit testing）
- 授权（License）