CODEWITHSUNDEEP
mysqlyii
Roy M J
Roy M J

Reputation: 6938

Sub queries in Yii

I have the following mysql query which works well in phpmyadmin when i execute it :

"SELECT * FROM accounts_users WHERE id = ( SELECT teacher_id FROM general_teacher_student_associations WHERE student_id = 509 )";

But when i execute via Yii, it breaks : 

    $query          = "SELECT * FROM accounts_users WHERE id = ( SELECT teacher_id FROM general_teacher_student_associations WHERE student_id =509 )";
    $command        = Yii::app()->db->createCommand($query);
    $teachers_list  = $command->query();
    return $teachers_list;

509 is a dynamically fetched value.

1. What am i doing wrong?

2. Can this be done in a better way?

/******Edited***********/

Found the error : The sub query returns more than one row. Can i use single query to fetch all the values other than using a foreach loop and then inside that executing another query?

Solution : (Accepting Daniels answer since his comment actually solved the issue)

    $query          = "SELECT * FROM accounts_users WHERE id IN ( SELECT teacher_id FROM general_teacher_student_associations WHERE student_id =509 )";
    $command        = Yii::app()->db->createCommand($query);
    $teachers_list  = $command->queryAll();
    return $teachers_list;

p.s: This is an edition work and i am not allowed to touch model and hence using model relations is out of window and thats why i ended up with this

Upvotes: 0

Views: 267

Answers (1)

Daniel Vaquero
Daniel Vaquero

Reputation: 1305

Try:

$teachers_list = Yii::app()->db->createCommand()->select('ausers.*')
        ->from('accounts_users ausers')
        ->join('( SELECT teacher_id FROM general_teacher_student_associations WHERE student_id = 509 ) as teachers ON teachers.teacher_id = ausers.id')
        ->queryRow();

Upvotes: 1

Related Questions