查询语言 PHQL
Phalcon查询语言，PhalconQL或者简单的称之为PHQL，是一种面向对象的高级SQL语言，允许使用标准化的SQL编写操作语句。 PHQL实现了一个解析器（C编写）来把操作语句解析成RDBMS的语法。
为了达到高性能，Phalcon实现了一个和 SQLite 中相似的解析器。它只占用了非常低的内存，同时也是线程安全的。
使用 PHQL 会自动通过模型检测查字段信息，作出简单的验证。使用查询构建器创建查询
使用查询构建器，我们就不需要书写 PHQL 语句了。
选取查询构建器
<?php
// Create a new Query Builder for Select
$robots = $this->modelsManager->createBuilder();
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_SELECT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createSelectBuilder();
// Getting a whole set
$builder->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->execute();
// Getting the first row
$builder->from('Robots')
->join('RobotsParts')
->orderBy('Robots.name')
->getQuery()
->getSingleResult();
效果和下面的代码一样:
<?php $phql = "SELECT Robots.* FROM Robots JOIN RobotsParts p ORDER BY Robots.name LIMIT 20"; $result = $manager->executeQuery($phql);
更多的例子:
<?php
// 'SELECT Robots.* FROM Robots';
$builder->from('Robots');
// 'SELECT Robots.*, RobotsParts.* FROM Robots, RobotsParts';
$builder->from(
array(
'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(array('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(array('Robots.name', 'Robots.id'));
// 'SELECT Robots.name, SUM(Robots.price) FROM Robots GROUP BY Robots.name';
$builder->columns(array('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(array('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(array('r' => 'Robots'))
->addFrom('Parts', 'p');
// 'SELECT r.*, p.* FROM Robots AS r, Parts AS p';
$builder->from(array('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', array(1, 2, 3));
// 'SELECT Robots.* FROM Robots WHERE id NOT IN (1, 2, 3)';
$builder->from('Robots')
->notInWhere('id', array(1, 2, 3));
// 'SELECT Robots.* FROM Robots WHERE name LIKE '%Art%';
$builder->from('Robots')
->where('name LIKE :name:', array('name' => '%' . $name . '%'));
// 'SELECT r.* FROM Store\Robots WHERE r.name LIKE '%Art%';
$builder->from(['r' => 'Store\Robots'])
->where('r.name LIKE :name:', array('name' => '%' . $name . '%'));
插入查询构建器
<?php
// Create a new Query Builder for Insert
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_INSERT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_INSERT);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createInsertBuilder();
// Insert two records
$builder->table('Robots')
->columns(array('name'))
->values(array(array('name' => 'Google'), array('name' => 'Baidu')))
->getQuery()
->execute();
更新查询构建器
<?php
// Create a new Query Builder for Update
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_UPDATE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_UPDATE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createUpdateBuilder();
// Update records
$builder->table('Robots')
->set(array('name' => 'Google'))
->getQuery()
->execute();
删除查询构建器
<?php
// Create a new Query Builder for Delete
$robots = $this->modelsManager->createBuilder(NULL, Phalcon\Mvc\Model\Query::TYPE_DELETE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::create(Phalcon\Mvc\Model\Query::TYPE_DELETE);
// Or
$builder = Phalcon\Mvc\Model\Query\Builder::createDeleteBuilder();
// Delete records
$builder->table('Robots')
->where('name = "Peter"')
->orderBy('Robots.id')
->limit(20)
->getQuery()
->execute();