Rails - SQL - объединяет и ОБЪЕДИНЯЕТ

#mysql #sql #ruby-on-rails #ruby

Вопрос:

У меня есть таблица учетных записей и таблица account_logins. Я пытаюсь выбрать каждую учетную запись, а затем упорядочить их по времени последнего входа в систему. Пока у меня есть следующее:

 accounts
  .confirmed_visible_with_completed_profile
  .joins(:logins)
  .select('accounts.*, max(account_logins.created_at) as last_sign_in_at')
  .group('accounts.id')

Это работает, однако, если с учетной записью не связано account_logins, она не возвращается в запросе. Мой SQL довольно плохой, но я нашел COALESCE через поиск в Google и попытался:

 accounts
  .confirmed_visible_with_completed_profile
  .joins(:logins)
  .select('accounts.*, COALESCE(max(account_logins.created_at), 0) as last_sign_in_at')
  .group('accounts.id')

Однако это по-прежнему не возвращает записи без связанного account_login . Из того, что я прочитал COALESCE(max(account_logins.created_at), 0) , должно возвращать 0, если max(account_logins.created_at) равно НУЛЮ, поэтому я не понимаю, почему это не работает. Любая помощь очень ценится

Ответ №1:

Вы пропустили эти значения в части соединения, и поэтому coalesce в select этом случае бесполезно. select применяется ли последняя операция. Вам нужно left join решить вашу проблему

в rails 5 у вас есть left_joins

 accounts
  .confirmed_visible_with_completed_profile
  .left_joins(:logins)
  .select('accounts.*, COALESCE(max(account_logins.created_at), 0) as last_sign_in_at')
  .group('accounts.id')

до rails 5

 accounts
  .confirmed_visible_with_completed_profile
  .joins("LEFT JOIN account_logins ON accounts.id = account_logins.account_id")
  .select('accounts.*, COALESCE(max(account_logins.created_at), 0) as last_sign_in_at')
  .group('accounts.id')

вам все еще нужно coalesce . с left join помощью имеет смысл

1. Спасибо за объяснение — я вижу, что теперь объединения исключат любые записи без account_login, прежде чем произойдет что-либо еще — ответ работает отлично 🙂

2. Мы могли бы немного повеселиться в rails < 5. .joins(Arel::Nodes::OuterJoin.new(AccountLogin.arel_table,nil).tap { |j| j.right = j.create_on(Account.arel_table[:id].eq(AccountLogin.arel_table[:account_id]))}) , я просто испытываю огромное отвращение к строковым соединениям 🙂

3. @engineersmnky я тоже, но я никогда не тратил время на изучение Arel: D

4. @Ursus учитывая ваш четкий опыт работы с ruby и rails, если на то пошло, я бы рекомендовал попытаться найти время, я знаю, что это действительно принесло мне пользу. То, что вы можете сделать, arel является удивительным и интегрируется в традиционные ActiveRecord запросы достаточно плавным образом.

5. Спасибо @engineersmnky, я сделаю это 🙂

Ответ №2:

Попробуйте использовать nullif

 COALESCE(nullif(max(account_logins.created_at),''), 0)