There is a database relation where some records are combined by a group_id.

I need to group all records by its group_id and get records from each group with the highest id.

One approach which works but is not ideal for performance on data with many records could be:

def self.newest_records
  all.group_by(&:group_id).map { |_id, records| records.max_by(&:id) }.flatten.compact
end

The following approach also works, but I think there's another way to get the records directly without the id lookup.

def self.newest_records
  ids = select(:id, :group_id).group(:group_id).pluck(Arel.sql('max(records.id)')) # get highest id of group
  Record.where(id: ids)
end

This will generate a huge SQL Command, which I think is not the best way.

SELECT "records".* FROM "records" INNER JOIN "relation" ON "records"."relation_id" = "relations"."id" WHERE "relations"."master_id" = 1 AND "records"."id" IN (1, 4, 5, 8, 10, 2, 3, 10000 others)

What might be a better solution to get the records by the highest id of a group directly without selecting them in the surrounding WHERE clause?

CodePudding user response：

Solved it with this command

Record.select('DISTINCT ON ("group_id") records.*').order(:group_id, 'records.id DESC')

Now I get directly the Records with the highest id of each group.

CodePudding user response：

You can do this:

Record.group(:group_id).maximum(:id)

And the output is

{
    'row23' => 5,
    'row15' => 3,
    'row16' => 4
}

Update:

Having record ids you can use them in other query

Record.where(id: Record.group(:group_id).maximum(:id).values)