Laravel 9:在select语句中使用子查询进行查询

Question

下午好,

我试图用Laravel执行这个查询：

SELECT COUNT(id) as total, (SUM(score) / COUNT(id)) as average,
            (SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 1 AND score < 2) as count_1,
            (SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 2 AND score < 3) as count_2,
            (SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 3 AND score < 4) as count_3,
            (SELECT COUNT(id) FROM rates Where book_id = :book_id AND score >= 4 AND score < 5) as count_4,
            (SELECT COUNT(id) FROM rates Where book_id = :book_id AND score = 5) as count_5
        FROM rates
        WHERE book_id = :book_id;

我一直在寻找在Laravel执行子查询的选择语句。我的最后意图是：

        return DB::table('rates')
        ->selectRaw([
                DB::raw('COUNT(id) as total'),
                DB::raw('(SUM(score) / COUNT(id)) as average'),
                '(' . DB::raw(DB::table('rates')->select(DB::raw('COUNT(id) as count_1'))->where('book_id', $bookId)->where('score', '>=', 1)->where('score', '<', 2)->toSql()) . ')',
                '(' . DB::raw(DB::table('rates')->select(DB::raw('COUNT(id) as count_2'))->where('book_id', $bookId)->where('score', '>=', 2)->where('score', '<', 3)->toSql()) . ')',
                '(' . DB::raw(DB::table('rates')->select(DB::raw('COUNT(id) as count_3'))->where('book_id', $bookId)->where('score', '>=', 3)->where('score', '<', 4)->toSql()) . ')',
                '(' . DB::raw(DB::table('rates')->select(DB::raw('COUNT(id) as count_4'))->where('book_id', $bookId)->where('score', '>=', 4)->where('score', '<', 5)->toSql()) . ')',
                '(' . DB::raw(DB::table('rates')->select(DB::raw('COUNT(id) as count_5'))->where('book_id', $bookId)->where('score', 5)->toSql()) . ')'
            ])
        ->where('book_id', $bookId)->get();

但我从Laravel那里得到了错误。

您知道如何使用Laravel的query在select语句中使用子查询执行MySQL查询吗？

(当然，我已经搜索了一个小时，但是在互联网上没有找到任何好的答案和堆栈溢出)。

Answer 1

我会以这种方式编写查询，这样它就不会使用任何子查询，并且只扫描表一次：

SELECT COUNT(id) as total, 
  (SUM(score) / COUNT(id)) as average,
  COUNT(CASE WHEN score >= 1 AND score < 2 THEN 1 END) AS count_1,
  COUNT(CASE WHEN score >= 2 AND score < 3 THEN 1 END) AS count_2,
  COUNT(CASE WHEN score >= 3 AND score < 4 THEN 1 END) AS count_3,
  COUNT(CASE WHEN score >= 4 AND score < 5 THEN 1 END) AS count_4,
  COUNT(CASE WHEN score >= 5 THEN 1 END) AS count_5
FROM rates
WHERE book_id = :book_id;

我不确定Laravel的语法，但可能是这样的：

return DB::table('rates')
    ->selectRaw([
            DB::raw('COUNT(id) as total'),
            DB::raw('SUM(score) / COUNT(id) as average'),
            DB::raw('COUNT(CASE WHEN score >= 1 AND score < 2 THEN 1 END) AS count_1'),
            ...others...
        ])
    ->where('book_id', $bookId)->get();

Answer 2

在文章的评论中，我查看了“高级子查询”的页面，我自己找不到它，并使用addSelect编写了这个函数。

public function getRateDataByBookId($bookId) {
    $sub_query_1 = DB::table('rates')->selectRaw('COUNT(id)')->where('book_id', $bookId)->where('score', '>=', 1)->where('score', '<', 2);
    $sub_query_2 = DB::table('rates')->selectRaw('COUNT(id)')->where('book_id', $bookId)->where('score', '>=', 2)->where('score', '<', 3);
    $sub_query_3 = DB::table('rates')->selectRaw('COUNT(id)')->where('book_id', $bookId)->where('score', '>=', 3)->where('score', '<', 4);
    $sub_query_4 = DB::table('rates')->selectRaw('COUNT(id)')->where('book_id', $bookId)->where('score', '>=', 4)->where('score', '<', 5);
    $sub_query_5 = DB::table('rates')->selectRaw('COUNT(id)')->where('book_id', $bookId)->where('score', 5);

    return DB::table('rates')
        ->selectRaw('COUNT(id) as total, (SUM(score) / COUNT(id)) as average')
        ->addSelect([
            'count_1' => $sub_query_1,
            'count_2' => $sub_query_2,
            'count_3' => $sub_query_3,
            'count_4' => $sub_query_4,
            'count_5' => $sub_query_5
            ])
        
        ->where('book_id', $bookId)
        ->first();
}

请注意，我使用first，因为我只期望得到一个结果