Squeel subquery

Question

I am trying to use a subquery to select the latest status of some records. I have the following query in SQL which I'd like to recreate in Rails using the squeel gem:

SELECT * FROM
changerequests c JOIN
    (SELECT changerequest_id,  max(created_at) max_created_at from 
     changerequest_statuses 
     GROUP BY changerequest_id) as max_status
ON c.id = max_status.changerequest_id
JOIN changerequest_statuses cs
ON cs.changerequest_id = c.id and max_status.max_created_at = cs.created_at
JOIN dim_change_statuses dcs
ON dcs.id = cs.dim_change_status_id

Following the guide here: https://github.com/activerecord-hackery/squeel#joins I have the following subquery:

subquery = ChangerequestStatus.group(:changerequest_id).select { [changerequest_id, max(created_at).as(max_date)] }

That works. However joining it up to the Changerequest model I get a NoMethodError: undefined method 'changerequest_id' for #<Hash> error

Changerequest.joins{[changerequest_statuses, subquery.as('max_status').on { id == max_status.changerequest_id}]}

This looks like identical syntax to me. What gives?

score 0 · Answer 1 · answered May 10 '16 at 09:31

I found that creating these scopes solved the problem:

In ChangerequestStatus.rb:

scope :max_id, -> {select('MAX(id) as max_id').group(:changerequest_id).map(&:max_id)}

In Changerequest.rb:

scope :max_condition, -> {where('changerequest_statuses.id IN (?)', ChangerequestStatus.max_id)}

It's only a partial solution because it only works with IDs not dates, and assumes later dated records will always have higher IDs which won't always be the case.

Squeel subquery

1 Answers1