DB Expression

Yii 2.0 General Discussions
1

I have a db expression which is breaking the sql statement with error SQLSTATE[42000]: Syntax error or access violation




$query = new Query();

$query->distinct(true);

$query->addSelect('email, cu.first_name, cu.last_name, cu.create_date, cu.id');

$query->from('customer cu');

$query->leftJoin('order o', 'o.customer_id = cu.id');

$exp = new Expression('(SUM(o.total_paid) - COALESCE(SUM(o.total_refunded),0)) AS spend');

$query->addSelect($exp);

This results in

SELECT DISTINCT email, cu.first_name, cu.last_name, cu.create_date, cu.id, (SUM(o.total_paid) - COALESCE(SUM(o.total_refunded), 0)) AS spend

You can see in the above that the expression has interpreted 0)) as a table or column name and quoted it. I think this might be a bug in yii\db\Connection::quoteSql() or i could just be doing it wrong!

Thanks

2

Query::select

 * Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should


 * use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts.

$query->addSelect([‘spend’ => ‘SUM(o.total_paid) - COALESCE(SUM(o.total_refunded),0)’]);