5

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.

Anton Rybalko
  • 1,041
  • 13
  • 21
rji rji
  • 627
  • 2
  • 14
  • 34

1 Answers1

2

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();
Anton Rybalko
  • 1,041
  • 13
  • 21
  • I am getting Column not found: 1054 Unknown column 'supcount' in 'where clause' if I write ->andWhere(new yii\db\Expression('IF(supcount>0, sup_project_status=1, project_status=1)')); – rji rji Mar 28 '18 at 09:15
  • Something wrong with column alias. Print query using `echo $uQuery->createCommand()->getRawSql();`. You'l see what's wrong. – Anton Rybalko Mar 28 '18 at 09:35
  • 1
    Yes. I got it. I found a solution with getRawSql(). Thank you – rji rji Mar 28 '18 at 10:42