Select last unique polymorphic objects ordered by created at in Rails

Question

I'm trying to get unique polymorphic objects by the value of one of the columns. I'm using Postgres.

The object has the following properties: id, available_type, available_id, value, created_at, updated_at.

I'm looking to get the most recent object per available_id (recency determined by created_at) for records with the available_type of "User".

I've been trying ActiveRecord queries like this:

Service.where(available_type: "User").order(created_at: :desc).distinct(:available_id)

But it isn't limiting to one per available_id.

TheRealMrCrowley · Answer 1 · 2017-06-09T21:47:54.710

Using a left join is going to be your probably most efficient way

The following sql selects only rows where there are no rows with a larger created_at.

See this post for more info: https://stackoverflow.com/a/27802817/5301717

query = <<-SQL
    SELECT m.*                    # get the row that contains the max value
    FROM services m                 # "m" from "max"
    LEFT JOIN services b        # "b" from "bigger"
        ON m.available_id = b.available_id    # match "max" row with "bigger" row by `home`
        AND m.available_type = b.available_type
        AND m.created_at < b.created_at    # want "bigger" than "max"
    WHERE b.created_at IS NULL      # keep only if there is no bigger than max
    AND service.available_type = 'User'
SQL

Service.find_by_sql(query)

distinct doesn't take a column name as an argument, only true/false.

distinct is for returning only distinct records and has nothing to do with filtering for a specific value.

if you need a specific available_id, you need to use where

e.g.

Service.distinct.where(available_type: "User").where(available_id: YOUR_ID_HERE).order(created_at: :desc)

to only get the most recent add limit

Service.distinct.where(available_type: "User").where(available_id: YOUR_ID_HERE).order(created_at: :desc).limit(1)

if you need to get the most recent of each distinct available_id, that will require a loop

first get the distinct polymorphic values by only selecting the columns that need to be distinct with select:

available_ids = Service.distinct.select(:available_id).where(available_type: 'User')

then get the most recent of each id:

recents = []
available_ids.each do |id|
  recents << Service.where(available_id: id).where(available_type: 'User').order(created_at: :desc).limit(1)
end

I don't need a specific `available_id`, I need one result per `available_id` (there are a handful of `available_ids`) — Andrew, Jun 08 '17 at 22:49
Would the `.where(available_id: YOUR_ID_HERE)` you have still be used? I don't need results for one `available_id`, I'd like one result for each `available_id`. — Andrew, Jun 08 '17 at 22:52
it will but in a different way, see the last section of my answer — TheRealMrCrowley, Jun 08 '17 at 22:56
Thank you for your answer. How performant is this solution? Are there any other ones that might be better or more efficient? — Andrew, Jun 08 '17 at 23:13
This would be an O(n) operation on the rails side. It's possible, but it would take mostly pure sql as far as I'm aware. — TheRealMrCrowley, Jun 09 '17 at 20:47
@Andrew I added an sql solution that will be more efficient than a ruby loop — TheRealMrCrowley, Jun 09 '17 at 21:51

score 1 · Accepted Answer · answered Jun 08 '17 at 23:15

1

Try

Service.where(id: Service
              .where(available_type: "User")
              .group(:available_id)
              .maximum(:id).values)

answered Jun 08 '17 at 23:15

Md. Farhan Memon

6,055
2
11
36

It spawns 2 consecutive queries at any point of time, independent of number of records. Whereas, the other solution is clearly n+1... – Md. Farhan Memon Jun 09 '17 at 01:44
This solution is not grabbing the most recent :created_at. Only the largest :id. In most cases they are the same thing, but just realize you might want to swap the :id with :created_at – TheRealMrCrowley Jun 09 '17 at 21:53
And remember that this is using a subquery, which has its own inefficiencies. I've update my answer with a join operation that will usually be more performant than a subquery – TheRealMrCrowley Jun 09 '17 at 21:54

Select last unique polymorphic objects ordered by created at in Rails

2 Answers2