Laravel groupBy then return one result with the highest count

user_2257418 0
Viewed 1816

I have an expenses table, that has a belongsTo relation with Descriptions. I simply want to return the description instance that was used the most. What my thoughts are, is to grab the description_id that was used the most, then grab the corresponding Description from the database using the id. So far I came up with this:

auth()->user()->expenses()->get()->groupBy('description_id');

So this gives me the results grouped by description_id. Unsure if I'm on the right track, but not sure where to go from here. Or if there is a more appropriate way to accomplish this. Anything that can lead me into the right direction will be appreciated.

laravel max php
3 Answers

You can try this way :

$userWithExpensesWithMaxDescription = auth()->user()->expenses()->with(['descriptions' => function ($query) {
    // subquery to get only max count
    $query->select('description_id', DB::raw('count(*) as total'))
        ->groupBy('description_id')
        ->orderBy('total', 'DESC')
        ->first();
}])->get();

I suppose you have relation descriptions in your Expenses model.

user_9996170

If I understood correctly, i think the following should work:

DB::table('expenses')
    ->where('user_id', auth()->user()->id)
    ->groupBy('description_id')
    ->orderByRaw('count(*) DESC')
    ->value('description_id')
    ->first();

That would return the description_id of the most popular expense.

user_78940270

Assuming you have an expenses relationship set up in your Description model and a user relationship set up in your Expense model you could do something like:

$description = Description::withCount([
    'expenses' => function ($query) {
        $query->where('user_id', auth()->id());
    }])
    ->orderBy('expenses_count', 'desc')
    ->first();
user_10336540
Related