Problem with ROUND function in sql query

Yii 2.0 General Discussions
1

$query = UserOrder::find()
->alias(‘o’)
->select(‘o.id, o.name, o.status, o.entry_date, o.confirm_date,
o.user_id, u.client_name, ug.group_name,
ROUND(SUM(op.weight*op.price) OVER(PARTITION BY op.user_order_id),2) AS sum’
->joinWith(‘user u’)
->joinWith(‘user.userGroup ug’)
->joinWith(‘userOrderProduct op’)
->distinct()
;

gives following error:

Database Exception – yii\db\Exception

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘FROM user_order o LEFT JOIN user u ON o.user_id = u.id LEFT JOIN’ at line 1

The SQL being executed was: SELECT COUNT() FROM (SELECT DISTINCT o.id, o.name, o.status, o.entry_date, o.confirm_date, o.user_id, u.client_name, ug.group_name, (ROUND(SUM(op.weightop.price) OVER(PARTITION BY op.user_order_id), 2) AS sum FROM user_order o LEFT JOIN user u ON o.user_id = u.id LEFT JOIN user ON o.user_id = user.id LEFT JOIN user_group ug ON user.group_id = ug.id LEFT JOIN user_order_product op ON o.id = op.user_order_id) c

it seems the problem is: ROUND(SUM(op.weight*op.price) OVER(PARTITION BY op.user_order_id), 2) AS sum
That comma in ROUND function confuses Yii2, and my beautiful ,2) gets fudged to ,2)

HOW DO I WRITE THOSE SQL UPPER COMMAS IN FORUM ?
Yii2 query is not displayed correctly here ! Anyway, it looks like query gets “exploded” with delimeter “,” and then Yii2 adds those upper commas. Problem is SQL ROUND function has a comma in it ROUND(somenum, howmanyaftercomma) ; and in the query it gets split in half:
“ROUND(somenum” and “howmanyaftercomma)”

2 
The SQL being executed was: SELECT COUNT(*) FROM (SELECT DISTINCT `o`.`id`, `o`.`name`, `o`.`status`, `o`.`entry_date`, `o`.`confirm_date`, `o`.`user_id`, `u`.`client_name`, `ug`.`group_name`, (ROUND(SUM(op.weight*op.price) OVER(PARTITION BY op.user_order_id), `2)` AS `sum` FROM `user_order` `o` LEFT JOIN `user` `u` ON `o`.`user_id` = `u`.`id` LEFT JOIN `user` ON `o`.`user_id` = `user`.`id` LEFT JOIN `user_group` `ug` ON `user`.`group_id` = `ug`.`id` LEFT JOIN `user_order_product` `op` ON `o`.`id` = `op`.`user_order_id`) `c`

My working query in phpmyadmin is:
SELECT DISTINCT uo.id, uo.name, uo.status, u.client_name, ug.group_name,
ROUND(SUM(op.weight*op.price) OVER(PARTITION BY uo.id), 2) as sum
FROM user_order uo
LEFT JOIN user u ON u.id=uo.user_id
LEFT JOIN user_group ug ON ug.id=u.group_id
LEFT JOIN user_order_product op ON op.user_order_id=uo.id

sry about changing some prefixes in the new query.

problem started when i changed from:

SUM(op.weight*op.price) OVER(PARTITION BY op.user_order_id) AS sum

to:

ROUND(SUM(op.weight*op.price) OVER(PARTITION BY op.user_order_id),2) AS sum

3

Try embedding the comma separated parameters in an array.

1 Like
4

allright this works now:
$query = UserOrder::find()
->alias(‘o’)
->select([‘o.id’, ‘o.name’, ‘o.status’, ‘o.entry_date’, ‘o.confirm_date’,
‘o.user_id’, ‘u.client_name’, ‘ug.group_name’,
‘SUM(ROUND(op.weight*op.price, 2)) OVER(PARTITION BY op.user_order_id) AS sum’])
->joinWith(‘user u’)
->joinWith(‘user.userGroup ug’)
->joinWith(‘userOrderProduct op’)
->distinct()
;

TY

1 Like