We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How can i tranlate this SQL to PHQL, Honor to have your Help

在学习Phalcon的过程中遇到了这样一句SQL,希望能够用PHQL去组成一个query进行查询,可以因为水平不够,所以不知道怎么写,求大神帮忙指点,感激不尽!

while i was learning Phalcon Framework, I have this sql ,but i can not translate it to PHQL to build a queryBuiler to make it easier for pagination, hope for your help .Thx a lot.

SELECT 
    `name`,`level`,`subject`,`count` 

FROM teacher 

WHERE

    (`level`=1)and(
        (`count`=0) OR 
        ((`count` IN (1, 4, 5) AND (`subject` LIKE '%physics%' OR `subject` LIKE '%chemistry%' OR `subject` LIKE '%maths%')) OR 
        (`count` = 2 AND (`subject` = 'physics,chemistry' OR `subject` = 'physics,maths' OR `subject` = 'chemistry,maths')) OR 
        (`count` = 3 AND `subject` = 'physics,chemistry,maths'))
    )

order by school_name,level,`subject` desc limit 3,15


77.6k
Accepted
answer
edited Dec '19

Rules of thumb for PHQL:

  • table names are mapped to classes, use the php class paths instead of sql table names
  • indexed parameter binding requires the index after the ? compared to PDO
  • named parameter binding requires a trailing : at the end of the parameter name, not just the beginning
  • PHQL aims to be dialect agnostic, so some features (sql functions) are not available in PHQL, but your example doesnt contain any
// InjectionAware context, like controllers
$teachers = $this->modelsManager->createBuilder()
    ->addFrom(\MyProject\Models\Teacher::class) // the namespaced php class that the teacher table is mapped to
    ->where("(`level`=1) and (
        (`count`=0) OR 
        ((`count` IN (1, 4, 5) AND (`subject` LIKE '%physics%' OR `subject` LIKE '%chemistry%' OR `subject` LIKE '%maths%')) OR 
        (`count` = 2 AND (`subject` = 'physics,chemistry' OR `subject` = 'physics,maths' OR `subject` = 'chemistry,maths')) OR 
        (`count` = 3 AND `subject` = 'physics,chemistry,maths'))
    )")
    ->orderBy('school_name, level, subject DESC')
    ->limit(3,15)
    ->getQuery()
    ->execute();
foreach($teachers as $teacher) {
    // $teacher is an instance of a Model, you'd access columns like so:
    $teacher->getName();
    // or
    $teacher->name;
}