how to write if condition inside where condition based on subquery in yii2 query builder

Question

I have a subquery inside main query as below:

$subquery = (new Query())->select('COUNT(project_sup_id)')
    ->from('project_supervisors AS sup')
    ->where(['AND','sup.project_ref_id = p.project_id']);

$uQuery =(new Query())->select(['project_id','supcount' => $subquery])
    ->from('projects AS p')
    ->join('LEFT JOIN','project_supervisors AS sup','sup.project_ref_id = project_id')
    ->andWhere('IF(supcount>0, sup_project_status=1, project_status=1)');

I am trying to write where condition based on logic,i.e, if the count obtained from subquery is greater than zero then where condition must be sup_project_status=1 else project_status=1. In normal Mysql it is easy to write if condition inside where, but in yii2 I am not understanding how to write.

Answer 1

Try to use yii\db\Expression for RDBMS-native expressions:

->andWhere(new yii\db\Expression('IF(supcount>0, sup_project_status=1, project_status=1)'));

You can check your SQL using:

echo $uQuery->createCommand()->getRawSql();

It returns prepared SQL command by Query object. Also you can write whole SQL as yii\db\Command:

$count = Yii::$app->db->createCommand('SELECT count(...) FROM ... WHERE ...')->queryScalar();