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


73.9k
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;
}