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

Created Dec '19	Last Reply Dec '19	Replies 1	Views 197	Votes 0

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

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