Как написать область действия Rails для этого SQL-запроса
У меня проблема с получением строк из базы данных по нескольким условиям.
У меня есть таблица Slot и SlotAlteration.
В слоте есть поле time_off_id.
SlotAlteration имеет два столбца: slot_id и action. Действие может быть 0 или 1. Каждый слот может иметь до двух записей в таблице SlotAlteration с действием 0 и 1 (не 0 и 0, а не 1 и 1) или вообще не иметь связей с SlotAlteration.
Запрос, который мне нужно преобразовать в область рельсов,
select *
from Slot s
where time_off_id is null
and not exists (
select 1 from Slot
left outer join SlotAlteration a
on s.id = a.slot_id
where a.action = 1
)
order by s.id;
Alterr — таблица SlotAlteration.
Это структура sql и запрос для визуального представления.
Моя модель слота имеет ассоциацию
has_many :alterations
scope :free, -> {
left_joins(:alterations)
.where(time_off_id: nil)
.where.not(alterations: { action: 1 })
}
Я попробовал это, но это не те слоты, в которых нет изменений.
Ответы 4
его будет легко отладить, если вы поделитесь соответствующим SQL-запросом, сгенерированным в написанной вами области.
Если необходимо включить значения Not NULL, вы также можете убедиться в этом, выбрав в запросе ненулевые значения вместе с !=1.
Это может помочь вам.
используйте предложение «where» вместо «where»! и также включите ноль.
scope :free, -> {
left_joins(:alterations)
.where(time_off_id: nil)
.where("alterations.id IS NULL OR alterations.action != ?", 1)
}
Я бы добавил специальную ассоциацию has_many для чередования с action = 1. Это означает, что ассоциации могут выглядеть так:
# in app/models/slot.rb
has_many :alternations, class_name: 'SlotAlternation'
has_many :action_1_alternations, -> { where(action: 1) }, class_name: 'SlotAlternation'
Тогда вы сможете запрашивать записи без такой связанной записи.
Slot
.where(time_off_id: nil)
.where.missing(:action_1_alternations)
Обратите внимание, что метод запроса отсутствующий был представлен в Ruby on Rails 6.1 и недоступен в версиях Rails до 6.1.
Не совсем. Левое соединение относится к ассоциации с областью действия, допускающей только записи action = 1. Это означает, что если ассоциации action_1_alternations отсутствуют, то левое соединение либо id: nil, либо было только action != 1 записей. Таким образом, ассоциация представляет собой подзапрос, а отсутствующее внешнее левое соединение не существует.
Мы можем сделать это, используя NOT IN() с таким подзапросом:
scope :free, -> {
where(time_off_id: nil)
.where.not(id:SlotAlteration.select(:slot_id).where(action: 1))
}
Это приведет к
SELECT
slots.*
FROM
slots
WHERE
slots.time_off_id IS NULL AND
slots.id NOT IN (
SELECT
slot_alterations.slot_id
FROM
slot_alterations
WHERE
slot_alterations.action = 1
)
Если вы действительно хотите использовать EXISTS, вы также можете использовать следующее:
scope :free, -> {
where(time_off_id: nil)
.where.not(
SlotAlteration.select(1)
.where(action: 1)
.where(SlotAlteration.arel_table[:slot_id].eq(Slot.arel_table[:id))
.arel.exists)
}
Это произведет:
SELECT
slots.*
FROM
slots
WHERE
slots.time_off_id IS NULL AND
NOT ( EXISTS (
SELECT
1
FROM
slot_alterations
WHERE
slot_alterations.action = 1 AND slot_alterations.slot_id = slots.id
))
К сожалению, Rails не поддерживает условие EXISTS, хотя зачастую это более эффективный метод запроса, чем JOIN.
Вы можете прочитать основное мнение о Rails в этом выпуске
Но вы можете использовать внешние драгоценные камни, например where_exists
# This taken from @spickermann answer
# app/models/slot.rb
has_many :alternations, class_name: 'SlotAlternation'
has_many :action_1_alternations, -> { where(action: 1) }, class_name: 'SlotAlternation'
И после этого:
Slot
.where(time_off_id: nil)
.where_not_exists(:action_1_alternations)
После этого вы можете использовать .missing вместо дополнительного драгоценного камня. ``` .where.missing(:action_1_alternations)``` Но ответ полностью закрыл мою проблему!
@RomanMin missing использует JOIN. EXISTS работает быстрее, особенно если у вас большие столы
Вы можете сделать EXISTS в Rails. В интерфейсе запросов для этого просто нет методов, и создавать их с помощью Arel несколько неудобно.
Он выбирает слоты с параметром alteration.action = 0, даже если у них есть alteration.action = 1, но он не должен включать эти строки.