ActiveRecord select based on custom uniqueness and maximum column value

Question

Apologies for the title as I don't know how to succinctly describe my issue.

Let's assume we have multiple Item instances in the database with the following columns: name, year and version. Example below:

| id | name      | year   | version |
|----|-----------|--------|---------|
| 1  | Blockhead | 2010   | 1       |
| 2  | Blockhead | 2010   | 2       |
| 3  | Jughead   | 2011   | 1       |

I want to only return results where the name and year are unique, but only return the latest version of that name/year uniqueness combination. i.e., I want to return:

| id | name      | year   | version |
|----|-----------|--------|---------|
| 2  | Blockhead | 2010   | 2       |
| 3  | Jughead   | 2011   | 1       |

If possible I'd like to not have a grouped dataset...but my knowledge of SQL/AR is limited in this regard and I don't know what the proper solution would be. I currently retrieve all records and then filter out the ones I don't want but it's an inelegant solution.

@items = ::Item.active.paginate(per_page: 30, page: page || 1).to_a

# Do not show a given item unless it's 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

score 0 · Answer 1 · edited May 23 '17 at 11:54

Take a look to related SO question: Retrieving the last record in each group

In your case the simplest query to select necessary data will be:

SELECT name, year, MAX(version) as version
FROM items
GROUP BY name, year;

If you want to select additional columns (id for example) or to avoid grouped result, you may do it with subquery:

SELECT * FROM items
WHERE id IN (
  SELECT MAX(id)
  FROM items
  GROUP BY name, year);

This code assume MAX(id) gives id of the row with the latest version in the group.

Rails code for this query:

sub_query = Item.select("MAX(id)").group(:name, year).to_sql
Items.where("id iN (?)", subquery)

ActiveRecord select based on custom uniqueness and maximum column value

1 Answers1