I have model Constant which looks like this:
slug | version| value
co2 | 1 | 10
co2 | 2 | 11
h2o | 1 | 8
h2so4 | 1 | 7
h2so4 | 2 | 7
h2so4 | 3 | 7
h2so4 | 4 | 7
And I need to retrieve all the unique slugs, but only their highest version. Meaning:
slug | version | value
co2 | 2 | 11
h2o | 1 | 8
h2so4 | 4 | 7
I think this might do it.
$constants = Constant::select('slug', 'version', 'value')
->whereRaw('version = (select max(version) from constants as c where c.slug = constants.slug)')
->get();
foreach ($constants as $constant) {
echo $constant->slug . " | " . $constant->version . " | " . $constant->value . "<br>";
}
Can you try with this query?
$constants = Constant::select('slug', \DB::raw('MAX(version) as max_version'), 'value')
->groupBy('slug')
->get();
@gych That's exactly what ChatGPT wants me to do, but I get
SQLSTATE[42000]: Syntax error or access violation: 1055 'db.constants.value' isn't in GROUP BY (Connection: mysql, SQL: select `slug`, MAX(version) as max_version, `value` from `constants` group by `slug`)
Which seems because I'm in a strict mode.
@andyandy Ok, it's funny that ChatGPT gave you the exact same query. It seems to be caused by the 'value' column added to the select query.
This can be solved by removing 'value' from the first query and then adding it via an inner join query or by making a separate query after the first one to get the 'values' related to the initial results.
Can this work ?
$constants = Constant::select('slug', \DB::raw('MAX(version) as max_version'), 'value')
->groupBy(['slug','value'])
->get();
Please or to participate in this conversation.