Yii2 How to translate SUM SQL function to Query Builder?

I have this simple SQL query:

SELECT product_name, SUM (product_amount) 
FROM orders
GROUP BY product_name;

It will show a list with product names and their amounts. Like this example:

I want to translate it to Yii2 Query Builder. I don’t know how to use the SUM function. I tried this but it didn’t work:

Orders::find()
    ->select(
        [
            Orders::tableName() . ".product_name",
            (new yiidbQuery())->sum(Orders::tableName() . ".product_amount")
        ]
    )
    ->groupBy(
        [
            Orders::tableName() . '.product_name',
            Orders::tableName() . '.product_amount'
        ]
    );

Answer

You need to use yiidbExpression while selecting as you are trying to call the SQL SUM() function and you need not to quote the function while selecting.

Expression represents a DB expression that does not need escaping or quoting. Expression objects are mainly created for passing raw SQL expressions to methods of yiidbQuery, yiidbActiveQuery and related classes.

Change your code to

Orders::find()
    ->select(['product_name', new yiidbExpression('SUM(product_amount)')])
    ->groupBy('product_name,product_amount')
    ->all();