Query with eager loading with sum

I have an eloquent model setup like this (it's many to many relationship)

user

id | name

habit

id | name | impact

habit_user

id | user_id | habit_id

and I want to have all the users sorted by user completed habit's impact's sum in descending order

I tried this but it gives weird results

User::with([
            'habitUsers' => [
                'habit' => fn($q) => $q->sum('offset'),
            ]
      ])->get()

I know how to use this query manually but I want to do this query the Eloquent way.

ramirushi7997

3 years ago

Best Answer

Level 4

I did this in the end and it worked like a charm

$users = User::select(['id', 'name'])
        ->withSum('habits', 'impact')  // this will sum habit relationship's impact column
        ->orderBy('habits_sum_offset')
        ->whereIn('postal_code', $pgZipCodes)
        ->paginate(10);

Please or to participate in this conversation.