[FIXED] How do I use a main query value as a filter in a Subquery in cake php

Issue

How do I translate this into orm that uses the value form the main query in the sub query Here is my sql subquery

(select ups.username from users as ups where ups.id=student_id) as StudentName

My main query gets the student_id from an inner join Here is my Orm Subquery

    ->select([
        'StudentName'=>'Users.username'])
    ->where(['Users.id'=>'students.studentid']); 

When I run the query StudentName shows up blank, but if I manually set the value like this it returns a result

    ->select([
        'StudentName'=>'Users.username'])
    ->where(['Users.id'=>'55']); 

Solution

When using the key => value syntax, the right hand side value will always be subject to binding/escaping, unless it's an expression object. So your condition will bind Students.student_id as a literal string, or possibly as an integer, ie you end up with SQL like:

WHERE Users.id = 'Students.student_id'

or

WHERE Users.id = 0

Either pass a \Cake\Database\Expression\IdentifierExpression object, like:

->where(['Users.id' => $mainQuery->identifier('Students.student_id')])

or in older CakePHP versions:

->where(['Users.id' => new \Cake\Database\Expression\IdentifierExpression('Students.student_id')])

Or use the expression builder, which has support for comparing fields:

->where(function(\Cake\Database\Expression\QueryExpression $exp) {
    return $exp->equalFields('Users.id', 'Students.student_id');
})

There's an example for your use case in the advanced conditions docs (see the exists() example).

See also

• Cookbook > Database Access & ORM > Query Builder > Advanced Conditions

Answered By - ndm