Yii2 如何将 SUM SQL 函数转换为查询生成器? [英] Yii2 How to translate SUM SQL function to Query Builder?

问题描述

我有这个简单的 SQL 查询:

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:

我想把它翻译成 Yii2 Query Builder.我不知道如何使用 SUM 函数.我试过了，但没有用:

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

推荐答案

您需要使用 yiidbExpression 当您尝试调用 SQL SUM() 函数时，您需要选择不要在选择时引用函数.

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 表示一个 DB 表达式不需要转义或引用.表达式对象主要用于将原始 SQL 表达式传递给 yiidbQuery、yiidbActiveQuery 和相关类的方法.

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.

将您的代码更改为

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

这篇关于Yii2 如何将 SUM SQL 函数转换为查询生成器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！