Query builder not building the query correctly

What I'd like:

SELECT users.id, count(users.id) as payments_n FROM users INNER JOIN payments ON payments.user_id=users.id where users.user_type = 'user_type_1' GROUP BY users.id ORDER BY payments_n DESC

My attempt with Laravel:

$users = DB::table('users')
    ->where('user_type', 'user_type_1')
    ->join('payments', 'payments.user_id', 'users.id')
    ->select("users.id", DB::raw("count(users.id) as payments_n"))
    ->groupBy('users.id')
    ->orderBy('payments_n',  'desc');

The query laravel builds is ($users->toSql()):

select count(*) as aggregate from `users` inner join `payments` on `payments`.`user_id` = `users`.`id` where `user_type` = 'user_type_1' group by `users`.`id` order by `payments_n` desc

The problem is payments_n is missing because it uses count(*) as aggregate. Why doesn't it take in account ->select("users.id", DB::raw("count(users.id) as payments_n"))?

Sinnbeck

4 years ago

Level 102

.. Are you sure??

Route::get('test', function() {
    return DB::table('users')
        ->where('user_type', '=', 'user_type_1')
        ->join('payments', 'payments.user_id', 'users.id')
        ->select("users.id", DB::raw("count(users.id) as payments_n"))
        ->groupBy('users.id')
        ->orderBy('payments_n',  'desc')
        ->toSql();
});

results in

select [users].[id], count(users.id) as payments_n from [users] inner join [payments] on [payments].[user_id] = [users].[id] where [user_type] = ? group by [users].[id] order by [payments_n] desc

tykus

4 years ago

Level 104

That SQL query does not map the Builder; do you complete the query with a count Builder method in the chain?

Try finishing the query with get, and you can use selectRaw in place of DB::raw:

$users = DB::table('users')
    ->where('user_type', 'user_type_1')
    ->join('payments', 'payments.user_id', 'users.id')
    ->selectRaw("users.id, count(users.id) as payments_n")
    ->groupBy('users.id')
    ->orderBy('payments_n',  'desc')
    ->get();

Please or to participate in this conversation.