Phalcon - Query Language

Phalcon Query Language (PHQL) also called as PhalconQL is a high-level SQL dialect which standardizes SQL queries for the database systems supported by Phalcon.
It includes a parser, written in C, which translates the syntax in target RDBMS.
Here is a list of some of the prominent features of Phalcon query language −

• For security of web application, it uses bound parameters.
• Tables are treated as models whereas columns are treated as class attributes.
• All the data manipulation statements are used to prevent data loss which can occur.
• SQL injection is prevented by keeping SQL query call one at a time.

Creating a PHQL Query

Queries are created by instantiating class Phalcon\Mvc\Model\Query.

Example

// Instantiate the Query 
$query = new Query( 
   "SELECT * FROM Users", 
   $this->getDI() 
);  

// Execute the query returning a result if any 
$cars = $query->execute(); 

In the previous chapters, we have seen the working of scaffold web application named blog tutorial. It included searching categories as per name or slug.
Following is the code included for searchAction.

public function searchAction() {  
   $numberPage = 1; 
   if ($this->request->isPost()) { 
      $query = Criteria::fromInput($this->di, "Categories", $_POST); 
      $this->session->conditions = $query->getConditions(); 
   } else { 
      $numberPage = $this->request->getQuery("page", "int"); 
      if ($numberPage <= 0) { 
         $numberPage = 1; 
      } 
   } 
   
   $parameters = array(); 
   if ($this->session->conditions) { 
      $parameters["conditions"] = $this->session->conditions; 
   } 
   
   // $parameters["order"] = "id"; 
   $categories = Categories::find($parameters); 
   if (count($categories) == 0) { 
      $this->flash->notice("The search did not find any categories"); 
      
      return $this->dispatcher->forward(array( 
         "controller" => "categories", 
         "action" => "index" 
      )); 
   } 
   
   $paginator = new \Phalcon\Paginator\Adapter\Model(array( 
      "data" => $categories, 
      "limit"=> 10, 
      "page" => $numberPage 
   )); 
   
   $page = $paginator->getPaginate(); 
   $this->view->setVar("page", $page); 
}

The PHQL query executed (highlighted) in the controller will fetch all the results as per the search condition. The result of any search query as per condition will be displayed as in the screenshot.
Following is the output received on successful execution of the above code.

PHQL Life Cycle

Being a high-level language, PHQL provides the ability to the developers to personalize and customize various aspects as per requirements.
Following is the life cycle of each PHQL statement executed in Phalcon −

• Every PHQL statement is parsed and converted as an Intermediate Representation (IR) which is completely independent of the SQL implemented by the database system.
• The IR is converted to SQL statement as per the database system which is used in the web application. SQL statements generated are associated with the model.
• All PHQL statements are parsed once and cached in the memory. If the same statement result is executed, it will help in faster performance.