Home > Mobile >  GroupBy doesn't show all the data
GroupBy doesn't show all the data

Time:12-31

I have a list of sellers, where everyone paying service charge. I want to show the service charge grouped by year in descending order. The group's year should be in descending order. Like, I inputed data for 2023, 2020, 2021. The group data should show 2023 first then 2021, then 2020. First I tried with

$infos = Commision::all()->groupBy('country');

If I use order first it shows error. I have a previous question here. The I tried with

$infos = DB::table('service_charges')->groupBy('year')->orderBy('year','DESC')->get();
dd($infos);

But its show's only one data of each group. I have 3 data saved here . 2 is from 2021, 1 is from 2020. But the query showing me only one data from each group here.

CodePudding user response:

the GROUP BY SQL statement is used to aggregate data that have the same value for a given column. That means you can only get a single aggregated result of the data for each distinct value in that column (i.e. one value per year in your case).

The collection groupBy is used to group all data in their own collection if they have the same value. However the difference is that groupBy in the collection runs after the query so you can no longer orderBy at that point. Here are two ways you might be able to solve your issue:

  1. Order in the query then group the resulting collection
$infos = DB::table('service_charges')->orderBy('year','DESC')->get()->groupBy('year');
  1. Sort and order the resulting collection
$infos = Commision::all()->groupBy('country')->sortKeysDesc();

  • Related