Как мне выполнить запрос по двум столбцам с самореферентной связью с SQL / ActiveRecord?

#sql #ruby-on-rails #ruby #activerecord

Вопрос:

Итак, у меня есть Story и Share модель. Story Принадлежит a Share , а a Share имеет_one Story . У них обоих есть временные метки. Share является самоссылочным, поэтому в нем есть parent_id столбец, и иногда общий ресурс будет вложен под другим. И это не бесконечно вложимо, всего на один уровень ниже.

Изначально я просто сортировал по created_at столбцу Story . Но теперь мне нужно сделать что-то немного более сложное. Если у Story есть Share или вложенные общие ресурсы, я бы хотел также выполнить сортировку по created_at столбцам из них. Идея заключается в том, что дальнейшая активность с этим Story (вложенные ресурсы) поднимет его на вершину порядка.

РЕДАКТИРОВАТЬ: Вот решение:

 SELECT * FROM stories
LEFT OUTER JOIN shares AS s1 ON stories.share_id = s1.id
LEFT OUTER JOIN shares AS s2 ON s2.parent_id = s1.id
ORDERY BY s2.created_at DESC NULLS LAST, stories.created_at DESC

Или в ActiveRecord:

 Story.joins('LEFT OUTER JOIN shares AS s1 ON stories.share_id = s1.id').
      joins('LEFT OUTER JOIN shares AS s2 ON s2.parent_id = s1.id').
      order('s2.created_at DESC NULLS LAST, stories.created_at DESC')

Спасибо за помощь!

Ответ №1:

Вам придется дважды присоединяться к Share. В SQL это было бы что-то вроде этого:

 select * from stories
left outer join shares as s1 on s1.story_id=stories.id
left outer join shares as s2 on s1.parent_id=s2.id
order by greatest(s2.updated_at,s1.updated_at,stories.updated_at)

Не знаю, как это сделать в Rails, попробуйте поэкспериментировать с:включает

1. Ах, очень интересно. Хорошо, итак, внешний ключ на самом деле находится в Story, а не в Share. Итак, в Story есть столбец share_id. Я переключил его, и с одним соединением, похоже, все работает нормально, однако второе соединение, похоже, работает неправильно. В консоли postgres после первого объединения я вижу столбцы истории с добавленными (присоединенными) после них столбцами общего доступа. Но когда я выполняю второе объединение, добавляется второй набор столбцов общего доступа, но все они пусты. Я собираюсь отредактировать исходное сообщение, чтобы показать, что у меня есть на данный момент.

2. О, подождите. Разве это не должно быть s1.id = s2.parent_id ? Hrm, столбцы второго соединения по-прежнему пусты.

3. О, неважно, это действительно работает. Я только недавно очистил вложенный общий ресурс, поэтому внизу был только один, и я пропустил его. Спасибо!

4. Хорошо. Последняя часть; упорядочивание. Я начал только с вложенных created_at , так что это было бы s2.created_at . Если я это сделаю s2.created_at DESC , две строки со значениями для s2.created_at будут в правильном порядке, но они полностью внизу. Это помещает строки, которые имеют nulls для этого поля, перед строками с фактическими значениями. Как предотвратить это?

5. Хорошо, разобрался. В Postgres есть возможность переключать порядок сортировки нулей. По умолчанию это NULLS FIRST , поэтому просто измените его на NULLS LAST . Решение выше.