how to convert YII2 query "where" conditions from array to string

Question

I have an array of conditions for where() method, that can be successfully added to query builders:

 $condition = [
     'AND',
     ['archived' => 0],
     ['broken' => 0],
     ['gender' => 1],
     [
         'OR',
         ['visible' => 1],
         ['status' => 1],
     ],
 ];

but in some place I need to convert this array to string

(`archived` = 0) AND (`broken`= 0) AND (`gender` = 1) AND (`visible` = 1 OR `status` = 1)

and then place it to select() method like raw SQL statement

SUM(IF((`archived` = 0) AND (`broken`= 0) AND (`gender` = 1) AND (`visible` = 1 OR `status` = 1)))

Why do you need it to be a string, can't you use a subquery? — Raul Sauco
– Raul Sauco, Commented Nov 11, 2020 at 11:25

rob006 · Accepted Answer · 2020-11-11 16:19:47Z

1

You need to use buildCondition() and pass params to main query:

$sql = Yii::$app->db->queryBuilder->buildCondition($condition, $params);
$query
    ->select([
        'myField' => "SUM(IF($sql, 1, 0))"
    ])
    ->addParams($params);

answered Nov 11, 2020 at 16:19

rob006

22.3k5 gold badges57 silver badges77 bronze badges

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

2 Comments

Michal Hynčica Over a year ago

If the query is related to some ActiveRecord model it might be better to use its getDb() method instead of Yii::$app->db to make sure same connection component is used.

Volpacchiotto Over a year ago

by the way we cannot put creating a definite string into separate method. It should be used only at once in method where we get data

Collectives™ on Stack Overflow

how to convert YII2 query "where" conditions from array to string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related