Laravel 5.3. - how to use sum('column') from relation in where clause and orderBy it

Question

I have two tables: users and user_actions. I need to get all users where('available', true) (...and some other logic) and order them by sum of user_actions.clicks column.

public function actions()
{
        return $this->hasMany(UserActions::class);
}

I need something like this:

User::where('available', true)->where("something else....")->orderBy('sum(user_actions.clicks)', 'desc')->get();

The result must be a Collection of User class.

Amit Gupta · Accepted Answer · 2016-12-09T14:21:35.063

2

You can try DB::raw in the orderBy as:

User::where('available', true)
    ->where("something else....")
    ->join('user_actions', 'users.id', '=', 'user_actions.user_id')
    ->orderBy(DB::raw("sum('user_actions.clicks')"))
    ->groupBy('users.id')
    ->select('users.*')
    ->get();

Or you could use sortBy methods of the collection as:

User::where('available', true)->with('user_actions')->get()
    ->sortBy(function ($user) {
        return $user->user_actions->sum('clicks');
    });

Generally, you can handle this all on the at database end but this way is worth it because it greatly simplifies the process.

edited Dec 09 '16 at 14:21

answered Dec 09 '16 at 14:09

Amit Gupta

17,072
4
41
53

thanks, this was my first solution too, but if I use join, I get this error: "Syntax error or access violation: 1055 'compari_ci.users.created_at' isn't in GROUP BY ..." created_at is just another field in users table. I'm using Laravel 5.3 btw. – Andrei J Dec 09 '16 at 14:33
This is related to MySQL. Please check this answer http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Amit Gupta Dec 09 '16 at 14:35
If I do this, I only get the sum value, and no user attributes: `$query->join('user_actions', 'user_actions.user_id', '=', 'users.id'); $query->selectRaw('sum(user_actions.clicks) as clicks'); $query->groupBy('user_id'); $query->orderBy('clicks', 'desc');` – Andrei J Dec 09 '16 at 14:37
so you are suggesting to groupBy all user fields? this doesn't sound right to me. – Andrei J Dec 09 '16 at 14:39
Or you can try this solution https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2 – Amit Gupta Dec 09 '16 at 14:41
If i set strict to false, I don't get any more errors about group by, but it does't apply the ordering. The query looks right to me: `select users.* from users inner join user_actions on users.id = user_actions.user_id where available = true group by properties.id order by sum('user_actions.clicks') desc` – Andrei J Dec 09 '16 at 14:53
It actually works with strict, the ordering is applied, I had to many ' in the query: `$query->orderBy(DB::raw("sum(**'user_actions.clicks'**)"), 'desc');`It should be: `$query->orderBy(DB::raw("sum(**user_actions.clicks**)"), 'desc');` . So setting strict to false works for me. Thanks @iCode – Andrei J Dec 09 '16 at 15:02

score 0 · Answer 2 · answered Dec 09 '16 at 14:08

0

You could do something like

->selectRaw('user_actions.clicks, sum(user_actions.clicks) as clicks')
->where('available', true)->where("something else....")
->orderBy('clicks', 'desc');

answered Dec 09 '16 at 14:08

scottevans93

1,119
1
9
25

Without grouping by users.id you will get only one row with a `SUM` of all clicks. – Vitalii Strimbanu Dec 09 '16 at 14:12

Laravel 5.3. - how to use sum('column') from relation in where clause and orderBy it

2 Answers2