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.

Can u provide me your sql query so can convert in yii query

Peerbits - Backend Team
– Peerbits - Backend Team

2018-03-28 10:26:26 +00:00
Commented Mar 28, 2018 at 10:26 — Peerbits - Backend Team
– Peerbits - Backend Team, Commented Mar 28, 2018 at 10:26

Anton Rybalko · Accepted Answer · 2018-03-28 10:51:42Z

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();

edited Mar 28, 2018 at 10:51

answered Mar 28, 2018 at 8:29

Anton Rybalko

1,31418 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rji rji Over a year ago

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)'));

Anton Rybalko Over a year ago

Something wrong with column alias. Print query using echo $uQuery->createCommand()->getRawSql();. You'l see what's wrong.

rji rji Over a year ago

Yes. I got it. I found a solution with getRawSql(). Thank you

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related