在Laravel中使用原始选择构建查询时SQL语法出错

iyfamqjs 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(135)

我正在尝试运行此查询

$revs = DB::table('reviews')->where('website_id', $website_id)
                    ->selectRaw('
                    COUNT(id) AS total_count,
                    COUNT(id) AS third_party_reviews_count WHERE review_source_id!=1,
                    COUNT(id) AS normal_reviews_count WHERE review_source_id=1,
                    COUNT(id) AS total_1_star WHERE stars = 1,
                    COUNT(id) AS total_2_star WHERE stars = 2,
                    COUNT(id) AS total_3_star WHERE stars = 3,
                    COUNT(id) AS total_4_star WHERE stars = 4,
                    COUNT(id) AS total_5_star WHERE stars = 5')->get();

但上面说

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 'COUNT(id) AS normal_reviews_count WHERE `review_source_id`=1,COUNT(id) AS

我尝试使用在线SQL语法检查器，但没有一个给我解决这个问题的方法。

laravel

来源：https://stackoverflow.com/questions/74448897/you-have-an-error-in-your-sql-syntax-while-building-query-with-raw-select-in-lar

1条答案

按热度按时间

eqqqjvef1#

您可以编写整个sql查询，然后像这样运行它：

$sql = <<<SQL
SELECT
    COUNT(id) AS total_count,
    SUM(CASE WHEN review_source_id != 1 THEN 1 ELSE 0 END) AS third_party_reviews_count,
    SUM(CASE WHEN review_source_id = 1 THEN 1 ELSE 0 END) AS normal_reviews_count,
    SUM(CASE WHEN stars = 1 THEN 1 ELSE 0 END) AS total_1_star,
    SUM(CASE WHEN stars = 2 THEN 1 ELSE 0 END) AS total_2_star,
    SUM(CASE WHEN stars = 3 THEN 1 ELSE 0 END) AS total_3_star,
    SUM(CASE WHEN stars = 4 THEN 1 ELSE 0 END) AS total_4_star,
    SUM(CASE WHEN stars = 5 THEN 1 ELSE 0 END) AS total_5_star
FROM
    reviews 
WHERE
    website_id = $website_id
SQL;

$revs = DB::select($sql);

赞(0）回复(0）举报 2022-11-18

我来回答

在Laravel中使用原始选择构建查询时SQL语法出错

1条答案

相关问题

热门标签

最新问答