0

I have a table that looks like the one below:

id age color
1 2 blue
3 3 red
4 5 green
5 5 purple

I want to get all the rows that have a unique age so something like the following would be returned:

id age color
1 2 blue
3 3 red
4 5 green 
$baseQuery = myModel::query()
->select('id', 'age', 'color')
->distinct('age')
->get()

This seems to return everything, but the second my select only has age column it removes the duplicates. The problem is i need the entire rows and not just age. Any ideas?

Edit: Another thing I tried was:

$baseQuery = myModel::query()
->select('id', 'age', 'color')
->groupBy('age')
->get()

This just threw a 1055 SQL error:

Syntax error or access violation: 1055 'table.color' isn't in GROUP BY

Thanks a bunch!

Edit I figured it out: Add the following before the query followed by the group by query above.

\DB::statement("SET SQL_MODE=''");
$baseQuery = myModel::query()
->select('id', 'age', 'color')
->groupBy('age')
->get()
ThatSWE
  • 1
  • 1

1 Answers1

0

There is a problem with your query logic and that's why you can't get the distinct row like that.

First of all, let's talk about your data that have age = 5. There are 2 rows, that contain age = 5 with the color green and purple. This is the question. If you want distinct age with other values returned to you, what should the query return to you? is it the data with the green color or with the purple one?

It doesn't make sense, isn't it? the query will never know what to return to you. that's why you don't create a query like that.

Fendy Harianto
  • 267
  • 1
  • 8
  • Good follow up. I should have been more explicit. It should return the first value and then ignore duplicates. That's what I was trying to show with my example. – ThatSWE Nov 23 '22 at 05:41
  • if you insist to do this, I think the best bet you can use is to use subquery and group by – Fendy Harianto Nov 23 '22 at 07:12
  • It seems like a common question see: https://stackoverflow.com/questions/25228823/how-to-get-distinct-values-for-non-key-column-fields-in-laravel I've added a solution to my original post. – ThatSWE Nov 23 '22 at 07:19