Rails: как выбрать записи, которые не имеют определенного связанного (связанного) объекта (краткое руководство по SQL EXISTS)

Question

Давайте предположим, что у нас есть пользователи:

class User < ActiveRecord::Base
  has_many :connections
  has_many :groups, through: :connections
end

И группы:

class Group < ActiveRecord::Base
  has_many :connections
  has_many :users, through: :connections
end

В основном, стандартное соединение "многие ко многим":

class Connection
  belongs_to :user
  belongs_to :group
end

Что я намерен сделать, это:

• Выберите только тех пользователей, которые не принадлежат данному набору групп (группы с идентификаторами [4,5,6])
• Выберите только пользователей, которые принадлежат к одному набору групп ([1,2,3]) и не принадлежат другому ([4,5,6])
• Выберите только пользователей, которые не принадлежат к группе

Кроме того, я не хочу:

• Получить много данных из базы данных, чтобы манипулировать ими с помощью кода Ruby. Я знаю, что это будет неэффективно с точки зрения ЦП и памяти (Ruby намного медленнее, чем любой обычно используемый движок БД, и обычно я хочу полагаться на движок БД для выполнения тяжелой работы)
• Я пробовал такие запросы как User.joins(:group).where(group_id: [1,2,3]).where.not(group_id: [4,5,6]) и они возвращают неправильные результаты (некоторые пользователи из набора результатов принадлежат к группам 4,5,6, а также 1,2,3)
• Я не хочу делать join просто ради проверки только на существование, потому что я знаю, что это довольно сложная (то есть процессор / интенсивная память) операция для БД

Answer 1

Такие вопросы довольно распространены среди начинающих разработчиков среднего уровня Rails. Ты знаешь ActiveRecord интерфейс и основные SQL операции, но вы наткнулись на такие задачи, как указано в вопросе. (Пара примеров таких вопросов: 1, 2).

Ответ прост: используйте SQL EXISTS состояние Краткий справочник с данного URL:

Синтаксис

Синтаксис для условия SQL EXISTS:

WHERE EXISTS ( subquery );

Параметры или Аргументы

подзапрос

Подзапрос является SELECT заявление. Если подзапрос возвращает хотя бы одну запись в своем наборе результатов, EXISTS условие будет оцениваться как истинное, а EXISTS условие будет выполнено. Если подзапрос не возвращает никаких записей, EXISTS условие будет оцениваться как ложное, а EXISTS условие не будет выполнено.

Также упоминается, что EXISTS может быть медленнее, чем JOIN, но обычно это не так. От Exists v. Присоединяйтесь к вопросу о SO:

EXISTS используется только для проверки, если подзапрос возвращает результаты и короткие замыкания, как только это произойдет. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation. [...] If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN, The exception is on very complicated subqueries, where it is normally quicker to use EXISTS,

So, the database doesn't need to look through all the connections (it stops 'joining' records with 'exists' as soon as it founds the right one), and doesn't need to return all the fields from the table joined (just check that the corresponding row, well, does exist).

Answering the specific questions:

Select only such users, who don't belong to given set of Groups (groups with ids [4,5,6])

not_four_to_six = User.where("NOT EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
   AND connections.group_id IN (?)
  )", [4,5,6])

Select only such users, who belong to one set of Groups ([1,2,3]) and don't belong to another ([4,5,6])

one_two_three = not_four_to_six.where("EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
   AND connections.group_id IN (?)
  )", [1,2,3])

Select only such users, who doesn't belong to a Group

User.where("NOT EXISTS (
   SELECT 1 FROM connections
   WHERE connections.user_id = users.id
  )")