It allows user to implement query language similar to SQL query language. PHQL is implemented as a parser which connects to RDBMS. Phalcon parser uses same technology as SQLite.
PHQL consist of features which are listed below:
- It secures the code using bound parameters.
- It prevents injection by executing one SQL statement per call.
- It ignores all comments which mostly used in SQL injections.
- It only allows data manipulation statement to execute.
PHQL Life Cycle
PHQL allows developers the ability to personalize and customize accordingly. The following is the lifecycle of PHQL statement which is executed:
- The PHQL is parsed and converted into an Intermediate Representation (IR). IR is independent of the SQL implementation by database system.
- The IR is converted to valid SQL according to the database system.
- PHQL statements are parsed once and cached in memory. Further executions of the same statement result in a slightly faster execution.
Implementation
Front-end
First we create a front-end which takes the input.
We have 2 models Mobile and Brands:
<?php
use Phalcon\Mvc\Model;
class Mobile extends Model
{
public $id;
public $name;
public $brand_id;
public $price;
public $year;
/**
* This model is mapped to the table sample_mobile
*/
public function getSource()
{
return 'sample_mobile';
}
/**
* A mobile only has a Brand, but a Brand have many mobile
*/
public function initialize()
{
$this->belongsTo('brand_id', 'Brands', 'id');
}
}
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 Mobile
*/
public function initialize()
{
$this->hasMany('id', 'Mobile', 'brand_id');
}Creating PHQL Query
It is created under directory Phalcon\Mvc\Model\Query.
<?php
use Phalcon\Mvc\Model\Query;
// Instantiate the Query
$query = new Query(
'SELECT * FROM Mobile',
$this->getID()
);
// Execute the query returning a result if any
$mobile = $query->execute();Executing PHQL Query
It is executed from controller or view under directory Phalcon\Mvc\Model\Manager.
<?php
// Executing a simple query
$query = $this->modelsManager->createQuery('SELECT * FROM Mobile');
$mobile = $query->execute();
// With bound parameters
$query = $this->modelsManager->createQuery('SELECT * FROM Mobile WHERE name = :name:');
$mobile = $query->execute(
[
'name' => 'Sony',
]
); Output:
Result Types
Result type is of two types Simple and Complex depending upon the type of column we query.
If we retrieve single object then the object return is Simple Result (Phalcon\Mvc\Model\Resultset\Simple).
<?php
// Executing a simple query
$query = $this->modelsManager->createQuery('SELECT * FROM Mobiles);
$mobiles= $query->execute();
// With bound parameters
$query = $this->modelsManager->createQuery('SELECT * FROM Mobiles WHERE name = :name:');
$mobiles = $query->execute(
[
'name' => ?Sony',
]
); If we access both complete objects and scalars at once then return Complex Result (Phalcon\Mvc\Model\Resultset\Complex).
<?php
$phql = 'SELECT m.price*0.16 AS taxes, m.* FROM Mobiles AS m ORDER BY m.name';
$result = $manager->executeQuery($phql);
foreach ($result as $row) {
echo 'Name: ', $row->Mobiles->name, "\n";
echo 'Price: ', $row-> Mobiles ->price, "\n";
echo 'Taxes: ', $row->taxes, "\n";
}
Leave a Reply