Question 17374 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search