Phalcon 查詢語言（Phalcon Query Language (PHQL)） · phalcon框架

Phalcon查詢語言，PhalconQL或者簡單的稱之為PHQL，是一種面向對象的高級SQL語言，允許使用標準化的SQL編寫操作語句。 PHQL實現了一個解析器（C編寫）來把操作語句解析成RDBMS的語法。為了達到高性能，Phalcon實現了一個和 SQLite 中相似的解析器。它只占用了非常低的內存，同時也是線程安全的。 The parser first checks the syntax of the pass PHQL statement, then builds an intermediate representation of the statement and finally it converts it to the respective SQL dialect of the target RDBMS. 解析器首先檢查傳遞PHQL語句的語法，然后構建語句的中間件，最后將它轉換為目標RDBMS的各自的SQL方言。 In PHQL, we’ve implemented a set of features to make your access to databases more secure: 在PHQL中，我們實現了一組特性，以使您對數據庫的訪問更加安全: * Bound parameters are part of the PHQL language helping you to secure your code 綁定參數是PHQL語言的一部分，它幫助您確保代碼的安全。 * PHQL only allows one SQL statement to be executed per call preventing injections PHQL只允許在每次調用時執行一個SQL語句，防止注入 * PHQL ignores all SQL comments which are often used in SQL injections PHQL忽略SQL注入中經常使用的所有SQL注釋 * PHQL only allows data manipulation statements, avoiding altering or dropping tables/databases by mistake or externally without authorization PHQL只允許數據操作語句，避免錯誤地修改或刪除表/數據庫。 * PHQL implements a high-level abstraction allowing you to handle tables as models and fields as class attributes PHQL實現了一個高級抽象，允許您將表作為模型和字段作為類屬性來處理 #### 范例（Usage Example）為了更好的說明PHQL是如何使用的，在接下的例子中，我們定義了兩個模型 “Cars” and “Brands”: ~~~ <?php use Phalcon\Mvc\Model; class Cars extends Model { public $id; public $name; public $brand_id; public $price; public $year; public $style; /** * This model is mapped to the table sample_cars */ public function getSource() { return "sample_cars"; } /** * A car only has a Brand, but a Brand have many Cars */ public function initialize() { $this->belongsTo("brand_id", "Brands", "id"); } } ~~~ 然后每輛車(Car)都有一個品牌(Brand)，一個品牌(Brand)卻可以有很多輛車(Car): ~~~ <?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 可以非常容易 create/execute PHQL查詢: ~~~ <?php // Executing a simple query $query = $this->modelsManager->createQuery("SELECT * FROM Cars"); $cars = $query->execute(); // With bound parameters $query = $this->modelsManager->createQuery("SELECT * FROM Cars WHERE name = :name:"); $cars = $query->execute( [ "name" => "Audi", ] ); ~~~ 或者使用一種更簡單的執行方式: ~~~ <?php // Executing a simple query $cars = $this->modelsManager->executeQuery( "SELECT * FROM Cars" ); // Executing with bound parameters $cars = $this->modelsManager->executeQuery( "SELECT * FROM Cars WHERE name = :name:", [ "name" => "Audi", ] ); ~~~ 選取記錄（Selecting Records）和SQL類似，PHQL也允許使用SELECT來查詢記錄，但必須用模型類替換語句的表名: ~~~ <?php $query = $manager->createQuery( "SELECT * FROM Cars ORDER BY Cars.name" ); $query = $manager->createQuery( "SELECT Cars.name FROM Cars ORDER BY Cars.name" ); ~~~ 帶命名空間的模型類名也是允許的: ~~~ <?php $phql = "SELECT * FROM Formula\Cars ORDER BY Formula\Cars.name"; $query = $manager->createQuery($phql); $phql = "SELECT Formula\Cars.name FROM Formula\Cars ORDER BY Formula\Cars.name"; $query = $manager->createQuery($phql); $phql = "SELECT c.name FROM Formula\Cars c ORDER BY c.name"; $query = $manager->createQuery($phql); ~~~ PHQL支持絕大多數的標準SQL語法，甚至非標準的SQL語法也支持，比如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）查詢結果的類型依賴于我們查詢時列的類型，所以結果類型是多樣化的。如果你獲得了一個完整的對象，那么這個對象是 Phalcon\Mvc\Model\Resultset\Simple 的實例。這樣的查詢結果集是一組完整的模型對象: ~~~ <?php $phql = "SELECT c.* FROM Cars AS c ORDER BY c.name"; $cars = $manager->executeQuery($phql); foreach ($cars as $car) { echo "Name: ", $car->name, "\n"; } ~~~ 下面這種方式的查詢結果集也是一樣的: ~~~ <?php $cars = Cars::find( [ "order" => "name" ] ); foreach ($cars as $car) { echo "Name: ", $car->name, "\n"; } ~~~ 完整的對象中的數據可以被修改，并且可以重新保存在數據庫中，因為它們在數據表里面本身就是一條完整的數據記錄。但是如下這種查詢方式，就不會返回一個完整的對象: ~~~ <?php $phql = "SELECT c.id, c.name FROM Cars AS c ORDER BY c.name"; $cars = $manager->executeQuery($phql); foreach ($cars as $car) { echo "Name: ", $car->name, "\n"; } ~~~ 我們只想要數據表中的一些字段，盡管返回的結果集對象仍然是 Phalcon\Mvc\Model\Resultset\Simple 的實例，但是卻不能認為是一個完整的對象。上述例子中，返回的結果集中的每個對象僅僅只有兩個列對應的數據。 These values that don’t represent complete objects are what we call scalars. PHQL allows you to query all types of scalars: fields, functions, literals, expressions, etc..: 這些不代表完整對象的值就是我們所說的標量。PHQL允許您查詢所有類型的標量:字段、函數、文本、表達式等等。 ~~~ <?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: 在本例中，結果是一個Phalcon\Mvc\Model\Resultset\Complex。這允許同時訪問完整的對象和標量: ~~~ <?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）通過PHQL可以非常方便的從多個模型中請求數據記錄。PHQL支持絕大多數的JOIN操作。 As we defined relationships in the models, PHQL adds these conditions automatically: 當我們在模型中定義關系時，PHQL自動地添加了這些條件: ~~~ <?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); ~~~ 也可以手動設置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: 另外，可以使用FROM子句中的多個表創建連接。 ~~~ <?php $phql = "SELECT Cars.*, Brands.* FROM Cars, Brands WHERE Brands.id = Cars.brands_id"; $rows = $manager->executeQuery($phql); foreach ($rows as $row) { echo "Car: ", $row->cars->name, "\n"; echo "Brand: ", $row->brands->name, "\n"; } ~~~ 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: 這段代碼在MySQL中執行以下SQL: ~~~ SELECT `artists`.`name`, `songs`.`name` FROM `artists` INNER JOIN `albums` ON `albums`.`artists_id` = `artists`.`id` INNER JOIN `songs` ON `albums`.`songs_id` = `songs`.`id` WHERE `artists`.`genre` = 'Trip-Hop' ~~~ #### 聚合（Aggregations） The following examples show how to use aggregations in PHQL: 下面的例子展示了如何在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: 條件允許我們過濾我們想要查詢的記錄集。WHERE子句允許這樣做: ~~~ <?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: 另外，作為PHQL的一部分，準備好的參數會自動地轉義輸入數據，引入更多的安全性: ~~~ <?php $phql = "SELECT * FROM Cars WHERE Cars.name = :name:"; $cars = $manager->executeQuery( $phql, [ "name" => "Lamborghini Espada" ] ); $phql = "SELECT * FROM Cars WHERE Cars.name = ?0"; $cars = $manager->executeQuery( $phql, [ 0 => "Lamborghini Espada" ] ); ~~~ #### 插入數據（Inserting Data） With PHQL it’s possible to insert data using the familiar INSERT statement: 使用PHQL，可以使用熟悉的insert語句插入數據: ~~~ <?php // Inserting without columns $phql = "INSERT INTO Cars VALUES (NULL, 'Lamborghini Espada', " . "7, 10000.00, 1969, 'Grand Tourer')"; $manager->executeQuery($phql); // Specifying columns to insert $phql = "INSERT INTO Cars (name, brand_id, year, style) " . "VALUES ('Lamborghini Espada', 7, 1969, 'Grand Tourer')"; $manager->executeQuery($phql); // Inserting using placeholders $phql = "INSERT INTO Cars (name, brand_id, year, style) " . "VALUES (:name:, :brand_id:, :year:, :style)"; $manager->executeQuery( $phql, [ "name" => "Lamborghini Espada", "brand_id" => 7, "year" => 1969, "style" => "Grand Tourer", ] ); ~~~ Phalcon doesn’t only transform the PHQL statements into SQL. All events and business rules defined in the model are executed as if we created individual objects manually. Let’s add a business rule on the model cars. A car cannot cost less than $ 10,000: 不僅將PHQL語句轉換為SQL，還可以將PHQL語句轉換為SQL語句。在模型中定義的所有事件和業務規則都被執行，就好像我們手動創建了單個對象一樣。讓我們在模型汽車上添加一個業務規則。一輛車的價格不低于1萬美元: ~~~ <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Message; class Cars extends Model { public function beforeCreate() { if ($this->price < 10000) { $this->appendMessage( new Message("A car cannot cost less than $ 10,000") ); return false; } } } ~~~ If we made the following INSERT in the models Cars, the operation will not be successful because the price does not meet the business rule that we implemented. By checking the status 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 to update records is UPDATE. When a record is updated the events related to the update operation will 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, [ 0 => 7000.00, 1 => 'Sedan', 2 => 5, ] ); ~~~ An UPDATE statement performs the update in two phases: UPDATE語句在兩個階段執行更新: * First, if the UPDATE has a WHERE clause it retrieves all the objects that match these criteria, 首先，如果更新有一個WHERE子句，它將檢索符合這些條件的所有對象 * 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) { $messages = $result->getMessages(); foreach ($messages as $message) { echo $message->getMessage(); } } ~~~ is somewhat equivalent to: 某種程度上相當于: ~~~ <?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(); ~~~ #### 刪除數據（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, [ "initial" => 1, "final" => 100, ] ); ~~~ DELETE operations are also executed in two phases like UPDATEs. To check if the deletion produces any validation messages you should check the status code returned: DELETE操作也在兩個階段中執行，比如更新。為了檢查刪除是否會產生任何驗證消息，您應該檢查返回的狀態碼: ~~~ <?php // Deleting multiple rows $phql = "DELETE FROM Cars WHERE id > 100"; $result = $manager->executeQuery($phql); if ($result->success() === false) { $messages = $result->getMessages(); foreach ($messages as $message) { echo $message->getMessage(); } } ~~~ #### 使用查詢構建器創建查詢（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: 可以使用構建器來創建PHQL查詢，無需編寫PHQL語句，也可以提供IDE工具: ~~~ <?php // Getting a whole set $robots = $this->modelsManager->createBuilder() ->from("Robots") ->join("RobotsParts") ->orderBy("Robots.name") ->getQuery() ->execute(); // Getting the first row $robots = $this->modelsManager->createBuilder() ->from("Robots") ->join("RobotsParts") ->orderBy("Robots.name") ->getQuery() ->getSingleResult(); ~~~ 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( [ "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 . "%"]); ~~~ #### 綁定參數（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:", ["name" => $name]) ->andWhere("type = :type:", ["type" => $type]) ->getQuery() ->execute(); // Passing parameters in query execution $robots = $this->modelsManager->createBuilder() ->from("Robots") ->where("name = :name:") ->andWhere("type = :type:") ->getQuery() ->execute(["name" => $name, "type" => $type]); ~~~ #### 禁止使用字面值（Disallow literals in PHQL） Literals can be disabled in PHQL, this means that directly using strings, numbers and boolean values in PHQL strings will be disallowed. If PHQL statements are created embedding external data on them, this could open the application to potential SQL injections: 在PHQL中可以禁用文字，這意味著在PHQL字符串中直接使用字符串、數字和布爾值是不允許的。如果創建了PHQL語句，將外部數據嵌入到它們中，那么這就可以為潛在的SQL注入打開應用程序: ~~~ <?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 exception will be thrown forcing the developer to use bound parameters. The same query can be written in a secure way like this: 如果文字是不允許的字符串可以作為PHQL語句的一部分，那么就會拋出一個異常，強制開發人員使用綁定的參數。同樣的查詢可以以這樣一種安全的方式編寫: ~~~ <?php $phql = "SELECT Robots.* FROM Robots WHERE Robots.name = :name:"; $result = $manager->executeQuery( $phql, [ "name" => $name, ] ); ~~~ You can disallow literals in the following way: 你可以用以下的方式來拒絕文字: ~~~ <?php use Phalcon\Mvc\Model; Model::setup( [ "phqlLiterals" => false ] ); ~~~ Bound parameters can be used even if literals are allowed or not. Disallowing them is just another security decision a developer could take in web applications. #### 轉義保留字（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 those words using the cross-database escaping delimiters ‘[‘ and ‘]’: PHQL有一些保留的單詞，如果您想將它們中的任何一個作為屬性或模型名，您需要使用跨數據庫轉義分隔符'和''來轉義這些單詞‘[‘ 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: 作為一種高級語言，PHQL為開發人員提供了個性化和定制不同方面的能力，以滿足他們的需求。以下是每一個PHQL語句的生命周期: The PHQL is parsed and converted into an Intermediate Representation (IR) which is independent of the SQL implemented by database system PHQL被解析并轉換為一個中間件表示(IR)，它與數據庫系統實現的SQL無關。 The IR is converted to valid SQL according to the database system associated to the model 根據與模型相關聯的數據庫系統，IR被轉換為有效的SQL。 PHQL statements are parsed once and cached in memory. Further executions of the same statement result in a slightly faster execution PHQL語句被解析一次并緩存在內存中。對同一語句的進一步執行會導致執行速度稍微快一些 #### 使用原生 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: 數據庫系統可以提供PHQL不支持的特定SQL擴展，在這種情況下，原始SQL可以是適當的: ~~~ <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Resultset\Simple as Resultset; class Robots extends Model { public static function findByCreateInterval() { // A raw SQL statement $sql = "SELECT * FROM robots WHERE id > 0"; // Base model $robot = new Robots(); // Execute the query return new Resultset( null, $robot, $robot->getReadConnection()->query($sql) ); } } ~~~ If Raw SQL queries are common in your application a generic method could be added to your model: 如果原始SQL查詢在您的應用程序中很常見，那么可以將通用方法添加到您的模型中: ~~~ <?php use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Resultset\Simple as Resultset; class Robots extends Model { public static function findByRawSql($conditions, $params = null) { // A raw SQL statement $sql = "SELECT * FROM robots WHERE $conditions"; // Base model $robot = new Robots(); // Execute the query return new Resultset( null, $robot, $robot->getReadConnection()->query($sql, $params) ); } } ~~~ The above findByRawSql could be used as follows: 上面的findByRawSql可以如下所述: ~~~ <?php $robots = Robots::findByRawSql( "id > ?", [ 10 ] ); ~~~ #### 注意事項（Troubleshooting） Some things to keep in mind when using PHQL: 在使用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. 類是區分大小寫的，如果一個類沒有被定義為與它所創建的相同的名稱，這可能會導致在操作系統中出現意外的行為，例如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. 別名類并沒有被完全名稱空間的類所取代，因為這只發生在PHP代碼中，而不是在字符串中。 If column renaming is enabled avoid using column aliases with the same name as columns to be renamed, this may confuse the query resolver. 如果啟用了列重命名，可以避免使用與列同名的列別名，這可能會混淆查詢解析器。