<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ThinkChat2.0新版上線,更智能更精彩,支持會話、畫圖、視頻、閱讀、搜索等,送10W Token,即刻開啟你的AI之旅 廣告
                [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代碼中,而不是在字符串內。 * 如果啟用了列重命名,請避免使用與要重命名的列具有相同名稱的列別名,這可能會使查詢解析程序混淆。
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看