Group by 2 columns and show latest for each group

Published 10 months ago by Swaz

I'm trying to group by 2 columns and show the latest entry for each group, but can't get it working. Does anyone know how to do this?

Products Table

id | type_id | category_id | price | created_at

Current Data

1 | 5 | 10 | 25.00 | 2017-01-01
2 | 5 | 10 | 50.00 | 2017-01-03
3 | 8 | 10 | 75.00 | 2017-01-05

Expected Output

2 | 5 | 10 | 50.00 | 2017-01-03
3 | 8 | 10 | 75.00 | 2017-01-05

Actual Output

1 | 5 | 10 | 25.00 | 2017-01-01
3 | 8 | 10 | 75.00 | 2017-01-05

Here's what I'm currently trying

Product::select(DB::raw('*, max(created_at) as date'))
    ->orderBy('date')
    ->groupBy('type_id')
    ->groupBy('category_id')
    ->get();
Best Answer (As Selected By Swaz)
Swaz

I got it working by adding a boolean helper column to my table. I was trying to avoid this but it will actually help me in a number of places. Much easier.

Product::where('is_latest', 1)->get();
robrogers3

Don't do 'select *'

only select the columns your are grouping or have aggregates for.

so, assuming id is primary key you cannot use that in a group by.

it's weird here cause if you have 3 rows and you include the id column you should have 3 rows in your output, not 2. bizarre.

robrogers3
Product::select(DB::raw('type_id, category_id, max(created_at) as date'))
    ->orderBy('date')
    ->groupBy('type_id')
    ->groupBy('category_id')
    ->get();
Swaz
Swaz
10 months ago (57,835 XP)

Thanks for the reply. It does not seem to be working, I'm still getting the same results.

robrogers3

well you cant get the same output, because there is only 3 columns available now.

type_id, category_id, date

right?

you could add price but then you need to add that 2 the group by. that will get you the results your looking for 'here'. but if there is another entry like:

4 | 5 | 10 | 150.00 | 2017-01-03

then you'll get 3 rows back. (cause price has 2 possible values.

robrogers3

what are you trying to achieve?

show the latest purchases by type, and category?

and there should only be one permutation of type and category respectively?

ps. post your sql again.

Swaz
Swaz
9 months ago (57,835 XP)

Thanks for all your help with this.

Yes, I need to get the latest products where type and category are unique. Grouping by price won't work for this scenario. Maybe groupBy is not the way to go with this.

Product::select(DB::raw('price, type_id, category_id, max(created_at) as date'))
    ->orderBy('date')
    ->groupBy('type_id')
    ->groupBy('category_id')
    ->get();
Swaz
Swaz
9 months ago (57,835 XP)

I got it working by adding a boolean helper column to my table. I was trying to avoid this but it will actually help me in a number of places. Much easier.

Product::where('is_latest', 1)->get();
robrogers3

Cool. Can you mark it as solved?

Please sign in or create an account to participate in this conversation.