I'm using Rails 5.1 with PostgreSQL 9.5 and am new to Arel tables. I'm trying to create a query (which I hope to eventually chain with other scopes queries) which returns records where all of the associated records match the given input.
Given an array of weekday_ids: [1, 2, 5, 6]
, return only ranges
which have active time_slots
matching all the given weekday_ids
Models
class Range < ApplicationRecord
has_many :time_slots
end
class TimeSlot < ApplicationRecord
belongs_to :range
belongs_to :weekday
end
Working example which returns expected results
def self.qualified_time_slots(weekday_ids = nil)
weekday_ids ||= [1, 2, 5, 6]
qualified_ranges = Range.includes(:time_slots).all.map do |range|
active_time_slots = range.time_slots.where(active: true)
range if weekday_ids.all? { |day| active_time_slots.map(&:weekday_id).include? day }
end
# return
qualified_ranges.compact
end
Current non-working attempt at Arel Query to achieve the same as above method
Range.joins(
:time_slots
).where(
time_slots: { active: true }
).where(
TimeSlot.arel_table[:weekday_id].in(weekday_ids)
)
Expected Results
# Should return:
[
range: {
id: 1,
time_slots: [
{ weekday_id: 1 },
{ weekday_id: 2 },
{ weekday_id: 5 },
{ weekday_id: 6 },
]
},
range: {
id: 2,
time_slots: [
{ weekday_id: 0 },
{ weekday_id: 1 },
{ weekday_id: 2 },
{ weekday_id: 3 },
{ weekday_id: 4 },
{ weekday_id: 5 },
{ weekday_id: 6 },
]
}
]
# Should NOT return
[
range: {
id: 3,
time_slots: [
{ weekday_id: 1 },
{ weekday_id: 2 },
{ weekday_id: 5 },
]
},
range: {
id: 4,
time_slots: [
{ weekday_id: 0 },
{ weekday_id: 6 },
]
}
]
EDIT - In working though this, I've followed the examples from a Joe Celko article on Relational Division and created this raw SQL query which, appears to work, but has not yet bet thoroughly tested:
ActiveRecord::Base.connection.exec_query("
SELECT
ts.range_id
FROM
time_slots AS ts
WHERE
ts.active = true
AND
ts.weekday_id IN (#{weekday_ids.join(',')})
GROUP BY
ts.range_id
HAVING COUNT(weekday_id) >= #{weekday_ids.length};
")
I'm still testing this, but it looks like this is working:
Range.joins(
:time_slots
).where(
TimeSlot.arel_table[:active].eq(
true
).and(
TimeSlot.arel_table[:weekday_id].in(
weekday_ids
)
)
).group(
Range.arel_table[:id]
).having(
TimeSlot.arel_table[:weekday_id].count(true).gteq(weekday_ids.count)
)