I have a model that looks like this:
id | index | created_at
And example entries look like this:
id | index | created_at
--------------------------
0 | 0 | 2024-12-12 12:12:00
1 | 1 | 2024-12-12 12:12:00
2 | 2 | 2024-12-12 12:12:00
3 | 0 | 2030-12-12 12:12:00
4 | 1 | 2030-12-12 12:12:00
5 | 2 | 2030-12-12 12:12:00
Now I want to get the latest distinct entries of these, which would look like this:
id | index | created_at
--------------------------
3 | 0 | 2030-12-12 12:12:00
4 | 1 | 2030-12-12 12:12:00
5 | 2 | 2030-12-12 12:12:00
The exact query that I'm using now is:
$planets = PlanetStatus::where('warId', $warId)->orderBy('created_at', 'desc')->groupBy('index')->distinct('index')
->get();
Which gives me the distinct rows in that table. The problem is that the query is not returning them with the latest entry, but the first in the database.
What do I have to change to make my query work like expected?
Thanks for any help in advance!
I found a solution after crawling the web a bit more. This is what I came up with:
$planetsData = DB::select("SELECT b.`index`, `owner`, `health`, `regenPerSecond`, `players`, b.`created_at` FROM planet_statuses b JOIN (SELECT t.`index`, MAX(t.`created_at`) as created_at FROM planet_statuses t GROUP BY `index`) as indexes ON b.`index` = indexes.`index` AND b.created_at = indexes.`created_at`");
$planets = PlanetStatus::hydrate($planetsData);
Is this a good way of querying the data?
@kejax Try this it may be helpful for you.
$planets = PlanetStatus::where('warId', $warId)
->orderByDesc('created_at')
->distinct('index')
->limit(1)
->first();
@amitsolanki24_ Thank you, but I need a collection of every latest value for the distinct column. The query I'm using now is working so far.
@Kejax Okay than try this
$planets = PlanetStatus::where('warId', $warId)
->orderByDesc('created_at')
->distinct('index')
->get()
@Kejax It would be great if you provide some test data or table structure.
@amitsolanki24_ don't keep trying to use distinct with a column name. It does not work like that
@Snapey yes i forgot, we can use unique('index') on a collection but it will firstly fetch all data then filter
you cannot specify a column with distinct (well, you can, but it has no effect)
Distinct will select one row from identical rows. by Identical, all fields in the select need to have the exact same values, so, if the dates are different then both rows will be returned
@Snapey Ohhhhhhhh, is there some other way to achieve what I'm trying to do with ORM?
have a look at the relationship, latestOfMany
Please or to participate in this conversation.