CODEWITHSUNDEEP
databaselaraveleloquentsumrelation
Andrei J
Andrei J

Reputation: 35

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

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.

Upvotes: 1

Views: 771

Answers (2)

Amit Gupta
Amit Gupta

Reputation: 17658

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.

Upvotes: 2

scottevans93
scottevans93

Reputation: 1079

You could do something like 

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

Upvotes: 0

Related Questions