ActiveRecord selection based on custom uniqueness and maximum column value
Sorry for the title as I don't know how to summarize my problem.
Assume that the database has multiple instances Item
with the following columns: name
, year
and version
. See example below:
| id | name | year | version |
|----|-----------|--------|---------|
| 1 | Blockhead | 2010 | 1 |
| 2 | Blockhead | 2010 | 2 |
| 3 | Jughead | 2011 | 1 |
I only want to return results when the name and year are unique, but only return the latest version of that name / year uniqueness combination. that is, I want to return:
| id | name | year | version |
|----|-----------|--------|---------|
| 2 | Blockhead | 2010 | 2 |
| 3 | Jughead | 2011 | 1 |
If possible, I would not want to have a grouped dataset ... but my knowledge of SQL / AR is limited in this regard and I do not know what the correct solution would be. I am currently fetching all the records and then filtering out the ones I don't need, but this is an inelegant solution.
@items = ::Item.active.paginate(per_page: 30, page: page || 1).to_a
# Do not show a given item unless it the latest version
@items.delete_if do |item|
@items.any? do |other|
other.id != item.id &&
other.read_attribute(:name) == item.read_attribute(:name) &&
other.year == item.year &&
other.version > item.version
end
end
source to share
Take a look at the related SO question: Getting the last entry in each group
In your case, the simplest query to select the required data would be:
SELECT name, year, MAX(version) as version
FROM items
GROUP BY name, year;
If you want to select additional columns (for example id
) or avoid the grouped result, you can do so with a subquery:
SELECT * FROM items
WHERE id IN (
SELECT MAX(id)
FROM items
GROUP BY name, year);
This code assumes it MAX(id)
gives id
lines with the last one version
in the group.
Rails code for this request:
sub_query = Item.select("MAX(id)").group(:name, year).to_sql
Items.where("id iN (?)", subquery)
source to share