[TOC]
# Phalcon查詢語言(PHQL)
Phalcon查詢語言,PhalconQL或簡稱PHQ??L是一種高級,面向對象的SQL方言,允許使用標準化的類SQL語言編寫查詢。PHQL實現為解析器(用C編寫),用于轉換目標RDBMS的語法。
為了實現最高性能,Phalcon提供了一個使用與[SQLite](http://en.wikipedia.org/wiki/Lemon_Parser_Generator) 相同技術的解析器。該技術提供了一個內存小的解析器,內存占用非常少,同時也是線程安全的。
解析器首先檢查傳遞PHQL語句的語法,然后構建語句的中間表示,最后將其轉換為目標RDBMS的相應SQL方言。
在PHQL中,我們實現了一組功能,使您對數據庫的訪問更加安全:
* 綁定參數是PHQL語言的一部分,可幫助您保護代碼
* PHQL僅允許每次調用執行一個SQL語句以防止注入
* PHQL忽略SQL注入中經常使用的所有SQL注釋
* PHQL僅允許數據操作語句,避免錯誤地或在未經授權的情況下外部更改或刪除表/數據庫
* PHQL實現了高級抽象,允許您將表作為模型和字段處理為類屬性
## 用例
為了更好地解釋PHQL如何工作,請考慮以下示例。我們有兩個模型`Cars`和`Brands`:
```php
<?php
use Phalcon\Mvc\Model;
class Cars extends Model
{
public $id;
public $name;
public $brand_id;
public $price;
public $year;
public $style;
/**
* 此模型映射到表sample_cars
*/
public function getSource()
{
return 'sample_cars';
}
/**
* 一輛車只有一個Brand,但一個Brand有很多Cars
*/
public function initialize()
{
$this->belongsTo('brand_id', 'Brands', 'id');
}
}
```
每輛車都有一個品牌,所以品牌有很多車:
```php
<?php
use Phalcon\Mvc\Model;
class Brands extends Model
{
public $id;
public $name;
/**
* 模型品牌被映射到'sample_brands'表
*/
public function getSource()
{
return 'sample_brands';
}
/**
* 一個品牌可以有很多汽車
*/
public function initialize()
{
$this->hasMany('id', 'Cars', 'brand_id');
}
}
```
## 創建PHQL查詢
只需實例化 `Phalcon\Mvc\Model\Query`類即可創建PHQL查詢:
```php
<?php
use Phalcon\Mvc\Model\Query;
// 實例化查詢
$query = new Query(
'SELECT * FROM Cars',
$this->getDI()
);
// 執行返回結果的查詢(如果有)
$cars = $query->execute();
```
從控制器或視圖中,使用注入的`models manager`(`Phalcon\Mvc\Model\Manager`)可以輕松創建/執行它們:
```php
<?php
// 執行簡單的查詢
$query = $this->modelsManager->createQuery('SELECT * FROM Cars');
$cars = $query->execute();
// 帶有綁定參數
$query = $this->modelsManager->createQuery('SELECT * FROM Cars WHERE name = :name:');
$cars = $query->execute(
[
'name' => 'Audi',
]
);
```
或者只是執行它:
```php
<?php
// 執行簡單的查詢
$cars = $this->modelsManager->executeQuery(
'SELECT * FROM Cars'
);
// 帶有綁定參數
$cars = $this->modelsManager->executeQuery(
'SELECT * FROM Cars WHERE name = :name:',
[
'name' => 'Audi',
]
);
```
## 選取記錄
作為熟悉的SQL,PHQL允許使用我們所知道的SELECT語句查詢記錄,除了不使用指定表,我們使用模型類:
```php
<?php
$query = $manager->createQuery(
'SELECT * FROM Cars ORDER BY Cars.name'
);
$query = $manager->createQuery(
'SELECT Cars.name FROM Cars ORDER BY Cars.name'
);
```
命名空間中的類也是允許的:
```php
<?php
$phql = 'SELECT * FROM Formula\Cars ORDER BY Formula\Cars.name';
$query = $manager->createQuery($phql);
$phql = 'SELECT Formula\Cars.name FROM Formula\Cars ORDER BY Formula\Cars.name';
$query = $manager->createQuery($phql);
$phql = 'SELECT c.name FROM Formula\Cars c ORDER BY c.name';
$query = $manager->createQuery($phql);
```
PHQL支持大多數SQL標準,甚至是非標準指令,例如LIMIT:
```php
<?php
$phql = 'SELECT c.name FROM Cars AS c WHERE c.brand_id = 21 ORDER BY c.name LIMIT 100';
$query = $manager->createQuery($phql);
```
### 結果類型
根據我們查詢的列類型,結果類型會有所不同。如果檢索單個整個對象,則返回的對象是
`Phalcon\Mvc\Model\Resultset\Simple`。這種結果集是一組完整的模型對象:
```php
<?php
$phql = 'SELECT c.* FROM Cars AS c ORDER BY c.name';
$cars = $manager->executeQuery($phql);
foreach ($cars as $car) {
echo 'Name: ', $car->name, "\n";
}
```
這完全相同:
```php
<?php
$cars = Cars::find(
[
'order' => 'name'
]
);
foreach ($cars as $car) {
echo 'Name: ', $car->name, "\n";
}
```
可以修改完整對象并將其重新保存在數據庫中,因為它們代表關聯表的完整記錄。還有其他類型的查詢不返回完整對象,例如:
```php
<?php
$phql = 'SELECT c.id, c.name FROM Cars AS c ORDER BY c.name';
$cars = $manager->executeQuery($phql);
foreach ($cars as $car) {
echo 'Name: ', $car->name, "\n";
}
```
我們只請求表中的某些字段,因此不能將它們視為整個對象,因此返回的對象仍然是
`Phalcon\Mvc\Model\Resultset\Simple`類型的結果集。但是,每個元素都是一個標準對象,只包含請求的兩列。
這些不代表完整對象的值就是我們所說的標量。PHQL允許您查詢所有類型的標量:字段,函數,文字,表達式等:
```php
<?php
$phql = "SELECT CONCAT(c.id, ' ', c.name) AS id_name FROM Cars AS c ORDER BY c.name";
$cars = $manager->executeQuery($phql);
foreach ($cars as $car) {
echo $car->id_name, "\n";
}
```
由于我們可以查詢完整的對象或標量,我們也可以同時查詢兩個:
```php
<?php
$phql = 'SELECT c.price*0.16 AS taxes, c.* FROM Cars AS c ORDER BY c.name';
$result = $manager->executeQuery($phql);
```
在這種情況下的結果是對象 `Phalcon\Mvc\Model\Resultset\Complex`。這允許一次訪問完整對象和標量:
```php
<?php
foreach ($result as $row) {
echo 'Name: ', $row->cars->name, "\n";
echo 'Price: ', $row->cars->price, "\n";
echo 'Taxes: ', $row->taxes, "\n";
}
```
標量映射為每個“行”的屬性,而完整對象則映射為具有其相關模型名稱的屬性。
### 連接
使用PHQL從多個模型中請求記錄很容易。支持大多數種類的連接。當我們在模型中定義關系時,PHQL會自動添加這些條件:
```php
<?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";
}
```
默認情況下,假定為INNER JOIN。您可以在查詢中指定JOIN的類型:
```php
<?php
$phql = 'SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands';
$rows = $manager->executeQuery($phql);
$phql = 'SELECT Cars.*, Brands.* FROM Cars LEFT JOIN Brands';
$rows = $manager->executeQuery($phql);
$phql = 'SELECT Cars.*, Brands.* FROM Cars LEFT OUTER JOIN Brands';
$rows = $manager->executeQuery($phql);
$phql = 'SELECT Cars.*, Brands.* FROM Cars CROSS JOIN Brands';
$rows = $manager->executeQuery($phql);
```
也可以手動設置JOIN的條件:
```php
<?php
$phql = 'SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands ON Brands.id = Cars.brands_id';
$rows = $manager->executeQuery($phql);
```
此外,可以使用FROM子句中的多個表創建聯接:
```php
<?php
$phql = 'SELECT Cars.*, Brands.* FROM Cars, Brands WHERE Brands.id = Cars.brands_id';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
echo 'Car: ', $row->cars->name, "\n";
echo 'Brand: ', $row->brands->name, "\n";
}
```
如果使用別名重命名查詢中的模型,那么將使用這些別名來命名結果的每一行中的屬性:
```php
<?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";
}
```
當聯接模型與from模型具有多對多關系時,中間模型將隱式添加到生成的查詢中:
```php
<?php
$phql = 'SELECT Artists.name, Songs.name FROM Artists ' .
'JOIN Songs WHERE Artists.genre = "Trip-Hop"';
$result = $this->modelsManager->executeQuery($phql);
```
此代碼在MySQL中執行以下SQL:
```sql
SELECT `artists`.`name`, `songs`.`name` FROM `artists`
INNER JOIN `albums` ON `albums`.`artists_id` = `artists`.`id`
INNER JOIN `songs` ON `albums`.`songs_id` = `songs`.`id`
WHERE `artists`.`genre` = 'Trip-Hop'
```
### 聚合
以下示例顯示如何在PHQL中使用聚合:
```php
<?php
// 所有車的價格是多少?
$phql = 'SELECT SUM(price) AS summatory FROM Cars';
$row = $manager->executeQuery($phql)->getFirst();
echo $row['summatory'];
// 每個品牌有多少輛汽車?
$phql = 'SELECT Cars.brand_id, COUNT(*) FROM Cars GROUP BY Cars.brand_id';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
echo $row->brand_id, ' ', $row['1'], "\n";
}
// 每個品牌有多少輛汽車?
$phql = 'SELECT Brands.name, COUNT(*) FROM Cars JOIN Brands GROUP BY 1';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
echo $row->name, ' ', $row['1'], "\n";
}
$phql = 'SELECT MAX(price) AS maximum, MIN(price) AS minimum FROM Cars';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
echo $row['maximum'], ' ', $row['minimum'], "\n";
}
// 統計不同的二手品牌
$phql = 'SELECT COUNT(DISTINCT brand_id) AS brandId FROM Cars';
$rows = $manager->executeQuery($phql);
foreach ($rows as $row) {
echo $row->brandId, "\n";
}
```
### 條件
條件允許我們過濾我們想要查詢的記錄集。WHERE子句允許這樣做:
```php
<?php
// 簡單條件
$phql = 'SELECT * FROM Cars WHERE Cars.name = "Lamborghini Espada"';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.price > 10000';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE TRIM(Cars.name) = "Audi R8"';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.name LIKE "Ferrari%"';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.name NOT LIKE "Ferrari%"';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.price IS NULL';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.id IN (120, 121, 122)';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.id NOT IN (430, 431)';
$cars = $manager->executeQuery($phql);
$phql = 'SELECT * FROM Cars WHERE Cars.id BETWEEN 1 AND 100';
$cars = $manager->executeQuery($phql);
```
此外,作為PHQL的一部分,準備好的參數會自動轉義輸入數據,從而帶來更多安全性:
```php
<?php
$phql = 'SELECT * FROM Cars WHERE Cars.name = :name:';
$cars = $manager->executeQuery(
$phql,
[
'name' => 'Lamborghini Espada'
]
);
$phql = 'SELECT * FROM Cars WHERE Cars.name = ?0';
$cars = $manager->executeQuery(
$phql,
[
0 => 'Lamborghini Espada'
]
);
```
## 插入數據
使用PHQL,可以使用熟悉的INSERT語句插入數據:
```php
<?php
// 插入沒有列
$phql = 'INSERT INTO Cars VALUES (NULL, "Lamborghini Espada", '
. '7, 10000.00, 1969, "Grand Tourer")';
$manager->executeQuery($phql);
// 指定要插入的列
$phql = 'INSERT INTO Cars (name, brand_id, year, style) '
. 'VALUES ("Lamborghini Espada", 7, 1969, "Grand Tourer")';
$manager->executeQuery($phql);
// 使用占位符插入
$phql = 'INSERT INTO Cars (name, brand_id, year, style) '
. 'VALUES (:name:, :brand_id:, :year:, :style)';
$manager->executeQuery(
$phql,
[
'name' => 'Lamborghini Espada',
'brand_id' => 7,
'year' => 1969,
'style' => 'Grand Tourer',
]
);
```
Phalcon不僅將PHQL語句轉換為SQL。執行模型中定義的所有事件和業務規則,就像我們手動創建單個對象一樣。讓我們在模型車上添加一個商業規則。一輛車不能低于$10,000:
```php
<?php
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Message;
class Cars extends Model
{
public function beforeCreate()
{
if ($this->price < 10000) {
$this->appendMessage(
new Message('A car cannot cost less than $ 10,000')
);
return false;
}
}
}
```
如果我們在模型Cars中進行了以下 `INSERT` ,則操作將不會成功,因為價格不符合我們實施的業務規則。通過檢查插入的狀態,我們可以打印內部生成的任何驗證消息:
```php
<?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();
}
}
```
## 更新數據
更新行與插入行非常相似。您可能知道,更新記錄的指令是更新。更新記錄時,將對每行執行與更新操作相關的事件。
```php
<?php
// 更新單個列
$phql = 'UPDATE Cars SET price = 15000.00 WHERE id = 101';
$manager->executeQuery($phql);
// 更新多個列
$phql = 'UPDATE Cars SET price = 15000.00, type = "Sedan" WHERE id = 101';
$manager->executeQuery($phql);
// 更新多行
$phql = 'UPDATE Cars SET price = 7000.00, type = "Sedan" WHERE brands_id > 5';
$manager->executeQuery($phql);
// 使用占位符
$phql = 'UPDATE Cars SET price = ?0, type = ?1 WHERE brands_id > ?2';
$manager->executeQuery(
$phql,
[
0 => 7000.00,
1 => 'Sedan',
2 => 5,
]
);
```
`UPDATE` 語句分兩個階段執行更新:
* 首先,如果 `UPDATE` 有一個 `WHERE` 子句,它將檢索符合這些條件的所有對象,
* 其次,基于查詢的對象,它更新/更改將它們存儲到關系數據庫的請求屬性
這種操作方式允許事件,虛擬外鍵和驗證參與更新過程。總之,以下代碼:
```php
<?php
$phql = 'UPDATE Cars SET price = 15000.00 WHERE id > 101';
$result = $manager->executeQuery($phql);
if ($result->success() === false) {
$messages = $result->getMessages();
foreach ($messages as $message) {
echo $message->getMessage();
}
}
```
有點相當于:
```php
<?php
$messages = null;
$process = function () use (&$messages) {
$cars = Cars::find('id > 101');
foreach ($cars as $car) {
$car->price = 15000;
if ($car->save() === false) {
$messages = $car->getMessages();
return false;
}
}
return true;
};
$success = $process();
```
## 刪除數據
刪除記錄時,將對每行執行與刪除操作相關的事件:
```php
<?php
// 刪除單行
$phql = 'DELETE FROM Cars WHERE id = 101';
$manager->executeQuery($phql);
// 刪除多行
$phql = 'DELETE FROM Cars WHERE id > 100';
$manager->executeQuery($phql);
// 使用占位符
$phql = 'DELETE FROM Cars WHERE id BETWEEN :initial: AND :final:';
$manager->executeQuery(
$phql,
[
'initial' => 1,
'final' => 100,
]
);
```
`DELETE` 操作也分兩個階段執行,如 `UPDATE`。要檢查刪除是否產生任何驗證消息,您應該檢查返回的狀態代碼:
```php
<?php
// 刪除多行
$phql = 'DELETE FROM Cars WHERE id > 100';
$result = $manager->executeQuery($phql);
if ($result->success() === false) {
$messages = $result->getMessages();
foreach ($messages as $message) {
echo $message->getMessage();
}
}
```
## 使用查詢生成器創建查詢
可以使用構建器創建PHQL查詢,而無需編寫PHQL語句,還提供IDE工具:
```php
<?php
// 獲取整個集合
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->execute();
// 獲取第一行
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->getSingleResult();
```
這與以下相同:
```php
<?php
$phql = 'SELECT Robots.* FROM Robots JOIN RobotsParts p ORDER BY Robots.name LIMIT 20';
$result = $manager->executeQuery($phql);
```
更多生成器的例子:
```php
<?php
// 'SELECT Robots.* FROM Robots';
$builder->from('Robots');
// 'SELECT Robots.*, RobotsParts.* FROM Robots, RobotsParts';
$builder->from(
[
'Robots',
'RobotsParts',
]
);
// 'SELECT * FROM Robots';
$phql = $builder->columns('*')
->from('Robots');
// 'SELECT id FROM Robots';
$builder->columns('id')
->from('Robots');
// 'SELECT id, name FROM Robots';
$builder->columns(['id', 'name'])
->from('Robots');
// 'SELECT Robots.* FROM Robots WHERE Robots.name = 'Voltron'';
$builder->from('Robots')
->where("Robots.name = 'Voltron'");
// 'SELECT Robots.* FROM Robots WHERE Robots.id = 100';
$builder->from('Robots')
->where(100);
// 'SELECT Robots.* FROM Robots WHERE Robots.type = 'virtual' AND Robots.id > 50';
$builder->from('Robots')
->where("type = 'virtual'")
->andWhere('id > 50');
// 'SELECT Robots.* FROM Robots WHERE Robots.type = 'virtual' OR Robots.id > 50';
$builder->from('Robots')
->where("type = 'virtual'")
->orWhere('id > 50');
// 'SELECT Robots.* FROM Robots GROUP BY Robots.name';
$builder->from('Robots')
->groupBy('Robots.name');
// 'SELECT Robots.* FROM Robots GROUP BY Robots.name, Robots.id';
$builder->from('Robots')
->groupBy(['Robots.name', 'Robots.id']);
// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name';
$builder->columns(['Robots.name', 'SUM(Robots.price)'])
->from('Robots')
->groupBy('Robots.name');
// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name HAVING SUM(Robots.price) > 1000';
$builder->columns(['Robots.name', 'SUM(Robots.price)'])
->from('Robots')
->groupBy('Robots.name')
->having('SUM(Robots.price) > 1000');
// 'SELECT Robots.* FROM Robots JOIN RobotsParts';
$builder->from('Robots')
->join('RobotsParts');
// 'SELECT Robots.* FROM Robots JOIN RobotsParts AS p';
$builder->from('Robots')
->join('RobotsParts', null, 'p');
// 'SELECT Robots.* FROM Robots JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p';
$builder->from('Robots')
->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p');
// 'SELECT Robots.* FROM Robots
// JOIN RobotsParts ON Robots.id = RobotsParts.robots_id AS p
// JOIN Parts ON Parts.id = RobotsParts.parts_id AS t';
$builder->from('Robots')
->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p')
->join('Parts', 'Parts.id = RobotsParts.parts_id', 't');
// 'SELECT r.* FROM Robots AS r';
$builder->addFrom('Robots', 'r');
// 'SELECT Robots.*, p.* FROM Robots, Parts AS p';
$builder->from('Robots')
->addFrom('Parts', 'p');
// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(['r' => 'Robots'])
->addFrom('Parts', 'p');
// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(['r' => 'Robots', 'p' => 'Parts']);
// 'SELECT Robots.* FROM Robots LIMIT 10';
$builder->from('Robots')
->limit(10);
// 'SELECT Robots.* FROM Robots LIMIT 10 OFFSET 5';
$builder->from('Robots')
->limit(10, 5);
// 'SELECT Robots.* FROM Robots WHERE id BETWEEN 1 AND 100';
$builder->from('Robots')
->betweenWhere('id', 1, 100);
// 'SELECT Robots.* FROM Robots WHERE id IN (1, 2, 3)';
$builder->from('Robots')
->inWhere('id', [1, 2, 3]);
// 'SELECT Robots.* FROM Robots WHERE id NOT IN (1, 2, 3)';
$builder->from('Robots')
->notInWhere('id', [1, 2, 3]);
// 'SELECT Robots.* FROM Robots WHERE name LIKE '%Art%';
$builder->from('Robots')
->where('name LIKE :name:', ['name' => '%' . $name . '%']);
// 'SELECT r.* FROM Store\Robots WHERE r.name LIKE '%Art%';
$builder->from(['r' => 'Store\Robots'])
->where('r.name LIKE :name:', ['name' => '%' . $name . '%']);
```
### 綁定參數
查詢構建器中的綁定參數可以在執行時一次構造或過去查詢時設置:
```php
<?php
// 在查詢構造中傳遞參數
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->where('name = :name:', ['name' => $name])
->andWhere('type = :type:', ['type' => $type])
->getQuery()
->execute();
// 在查詢執行中傳遞參數
$robots = $this->modelsManager->createBuilder()
->from('Robots')
->where('name = :name:')
->andWhere('type = :type:')
->getQuery()
->execute(['name' => $name, 'type' => $type]);
```
## 在PHQL中禁止文字
可以在PHQL中禁用文字,這意味著不允許直接在PHQL字符串中使用字符串,數字和布爾值。如果創建了PHQL語句,并在其上嵌入外部數據,則可以打開應用程序以進行潛在的SQL注入:
```php
<?php
$login = 'voltron';
$phql = "SELECT * FROM Models\Users WHERE login = '$login'";
$result = $manager->executeQuery($phql);
```
如果 `$login` 更改為 `' OR '' = '`,則生成的PHQL為:
```sql
SELECT * FROM Models\Users WHERE login = '' OR '' = ''
```
無論數據庫中存儲的是什么,都始終`true`。
如果不允許文字,則可以將字符串用作PHQL語句的一部分,因此將拋出異常,迫使開發人員使用綁定參數。可以用以下安全方式編寫相同的查詢:
```php
<?php
$type = 'virtual';
$phql = 'SELECT Robots.* FROM Robots WHERE Robots.type = :type:';
$result = $manager->executeQuery(
$phql,
[
'type' => $type,
]
);
```
您可以通過以下方式禁用文字:
```php
<?php
use Phalcon\Mvc\Model;
Model::setup(
[
'phqlLiterals' => false
]
);
```
即使允許或不允許文字,也可以使用綁定參數。禁止它們只是開發人員可以在Web應用程序中采取的另一個安全決策。
## 轉義保留字
PHQL有一些保留字,如果要將它們中的任何一個用作屬性或模型名稱,則需要使用跨數據庫轉義分隔符 `[` 和 `]`來轉義這些字:
```php
<?php
$phql = 'SELECT * FROM [Update]';
$result = $manager->executeQuery($phql);
$phql = 'SELECT id, [Like] FROM Posts';
$result = $manager->executeQuery($phql);
```
根據當前運行應用程序的數據庫系統,分隔符會動態轉換為有效的分隔符。
## PHQL生命周期
作為一種高級語言,PHQL使開發人員能夠個性化和定制不同方面,以滿足他們的需求。以下是每個執行的PHQL語句的生命周期:
* 解析PHQL并將其轉換為中間表示(IR),它獨立于數據庫系統實現的SQL
* 根據與模型關聯的數據庫系統將IR轉換為有效的SQL
* PHQL語句被解析一次并緩存在內存中。進一步執行相同的語句會導致執行速度稍快
## 使用原始SQL
數據庫系統可以提供PHQL不支持的特定SQL擴展,在這種情況下,原始SQL可能是合適的:
```php
<?php
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;
class Robots extends Model
{
public static function findByCreateInterval()
{
// 原始SQL語句
$sql = 'SELECT * FROM robots WHERE id > 0';
// 基礎模型
$robot = new Robots();
// 執行查詢
return new Resultset(
null,
$robot,
$robot->getReadConnection()->query($sql)
);
}
}
```
如果原始SQL查詢在您的應用程序中很常見,則可以在模型中添加通用方法:
```php
<?php
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;
class Robots extends Model
{
public static function findByRawSql($conditions, $params = null)
{
// 原始SQL語句
$sql = 'SELECT * FROM robots WHERE $conditions';
// 基礎模型
$robot = new Robots();
// 執行查詢
return new Resultset(
null,
$robot,
$robot->getReadConnection()->query($sql, $params)
);
}
}
```
上面的`findByRawSql`可以用如下:
```php
<?php
$robots = Robots::findByRawSql(
'id > ?',
[
10
]
);
```
## 故障排除
使用PHQL時要記住的一些事項:
* 類是區分大小寫的,如果沒有使用與創建時相同的名稱定義類,這可能會導致在具有區分大小寫的文件系統(如Linux)的操作系統中出現意外行為。
* 必須在連接中定義正確的字符集才能成功綁定參數。
* 別名類不會被完整的命名空間類替換,因為這只發生在PHP代碼中,而不是在字符串內。
* 如果啟用了列重命名,請避免使用與要重命名的列具有相同名稱的列別名,這可能會使查詢解析程序混淆。
- 常規
- Welcome
- 貢獻
- 生成回溯
- 測試重現
- 單元測試
- 入門
- 安裝
- Web服務器設置
- WAMP
- XAMPP
- 教程
- 基礎教程
- 教程:創建一個簡單的REST API
- 教程:V?kuró
- 提升性能
- 教程:INVO
- 開發環境
- Phalcon Compose (Docker)
- Nanobox
- Phalcon Box (Vagrant)
- 開發工具
- Phalcon開發者工具的安裝
- Phalcon開發者工具的使用
- 調試應用程序
- 核心
- MVC應用
- 微應用
- 創建命令行(CLI)應用程序
- 依賴注入與服務定位
- MVC架構
- 服務
- 使用緩存提高性能
- 讀取配置
- 上下文轉義
- 類加載器
- 使用命名空間
- 日志
- 隊列
- 數據庫
- 數據庫抽象層
- Phalcon查詢語言(PHQL)
- ODM(對象文檔映射器)
- 使用模型
- 模型行為
- ORM緩存
- 模型事件
- 模型元數據
- 模型關系
- 模型事務
- 驗證模型
- 數據庫遷移
- 分頁
- 前端
- Assets管理
- 閃存消息
- 表單
- 圖像
- 視圖助手(標簽)
- 使用視圖
- Volt:模板引擎
- 業務邏輯
- 訪問控制列表(ACL)
- 注解解析器
- 控制器
- 調度控制器
- 事件管理器
- 過濾與清理
- 路由
- 在session中存儲數據
- 生成URL和路徑
- 驗證
- HTTP
- Cookies管理
- 請求環境
- 返回響應
- 安全
- 加密/解密
- 安全
- 國際化
- 國際化
- 多語言支持