Group by 2 columns and show latest for each group

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.

Product::select(DB::raw('type_id, category_id, max(created_at) as date'))
    ->orderBy('date')
    ->groupBy('type_id')
    ->groupBy('category_id')
    ->get();

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

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.

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.

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();

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();

Cool. Can you mark it as solved?