Laravel 9: make a query with subqueries in the select statement-CodePudding

Good afternoon,

I'm trying to execute this query with 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;

I have been looking for Away in Laravel to execute subqueries in the Select Statement. My last intent it has been with:

        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();

But I get errors from Laravel.

Do you know how to execute a MySQL query with subqueries in the select statement, using the Query BUilder of Laravel?

(And of course, I have been searching for 1 hour how to do and did not find any good answer on internet and Stack Overflow).

CodePudding user response：

I'd write the query this way, so it doesn't use any subqueries, and scans the table only once:

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;

I'm not sure about Laravel syntax, but it might be something like this:

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();

CodePudding user response：

Following @xenooooo advice in the comments of the post, I check the page of "advanced subqueries" I couldn't find it by my own and writed this function using 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();
}

Note that I use first because I expect only one result