0

I would like to fetch all records per day with highest priority (as defined in associated model)

I'm struggling to build this with activerecord (rails 4.2)

The problem is very similar to this one Get records with max value for each group of grouped SQL results except that the age would come from the second model

or also this one with activerecord how can I select records based on the highest value of a field?

Model 1: Workduration: 
    date, duration 
    belongs_to :timerule

Model 2: Timerule: 
    priority
    has_many :workdurations

I put together the data as follows (all in Workduration)

def self.withPrio
    select("workdurations.*, timerules.prio AS prio").joins(:timerule)
end

I couldn't find the proper way to build the LEFT OUTER JOIN (self-join) on it.

Try-And-Error-Code:

Workduration.withPrio.joins("left join ? workdurations.date = wd2.date and workdurations.prio < wd2.prio", Workduration.withPrio)

Any help is appreciated!

Fabian
  • 11
  • 3

1 Answers1

1

I ended up doing this with (a big) find_by_sql and a second query to keep the scopes chainable:

  scope :maxPrioIds, ->{find_by_sql('SELECT o.*
                                  FROM
                                    (SELECT  workdurations.*, timerules.prio AS prio FROM "workdurations" INNER JOIN "timerules" ON "timerules"."id" = "workdurations"."timerule_id") o
                                  LEFT JOIN (SELECT  workdurations.*, timerules.prio AS prio FROM "workdurations" INNER JOIN "timerules" ON "timerules"."id" = "workdurations"."timerule_id") b
                                  ON o.date = b.date AND o.prio < b.prio
                                  WHERE b.prio is NULL').map(&:id)}
  scope :relevant, -> {where(id: Workduration.maxPrioIds)}
Fabian
  • 11
  • 3