Приложение Rails 3 с PostgreSQL – Получение списка сообщений, сгруппированных с помощью конвертера

Я запускаю приложение rails 3, которое моделирует сообщения электронной почты. Приложение развернуто на Heroku, поэтому backend db – PostgreSQL. Темы сообщений просты, смоделированные полем thread_id в таблице Posts. Когда пользователь публикует новое сообщение, назовем его p1, затем p1.thread_id = p1.id. Если пользователь отвечает на p1 с p2, то p2.thread_id = p1.thread_id.

Мне нужно написать запрос, чтобы выбрать сообщения, отправленные определенному пользователю. Полученный список должен содержать только одно сообщение для потока, и это сообщение должно быть последним сообщением в потоке. Мне также нужно знать, сколько сообщений в каждом потоке.

Используя следующий выбор:

SELECT DISTINCT ON(thread_id) * FROM "posts" 

не работает, так как он не возвращает сообщение, отсортированное последним первым.

Это тоже не работает:

SELECT DISTINCT ON (thread_id) * FROM "posts" ORDER BY thread_id, posts.created_at DESC

так как сообщения упорядочиваются сначала thread_id.

Стол сообщений:

 create_table "posts", :force => true do |t| t.string "title" t.text "content" t.string "content_type" t.text "options" t.string "type" t.integer "receiver_id" t.integer "sender_id" t.integer "circle_id" t.text "data_bag" t.datetime "created_at" t.datetime "updated_at" t.integer "parent_id" t.integer "thread_id" t.datetime "posted_at" end 

Спасибо за любую помощь.

Если вы не против того, чтобы ваши руки были грязными с небольшим количеством SQL, вы можете использовать функцию окна, чтобы выполнить задание. Вы можете получить идентификаторы сообщений с этим SQL:

 select id from ( select id, rank() over (partition by thread_id order by created_at desc) from posts where receiver_id = #{user.id} ) as dt where rank = 1 

Если вы хотите, чтобы дополнительные столбцы добавляли их в оба предложения SELECT. #{user.id} – это, конечно, получатель, который вас интересует.

Интересной частью является функция окна:

 rank() over (partition by thread_id order by created_at desc) 

Это разделит таблицу на группы на основе thread_id (вроде локализованного GROUP BY), закажите их по метке времени (самая последняя из первых), а затем rank() дает 1 для первой записи в каждой группе, 2 для второй , и т.д.

Учитывая таблицу, которая выглядит так:

 => select * from posts; id | receiver_id | thread_id | created_at ----+-------------+-----------+--------------------- 1 | 1 | 2 | 2011-01-01 00:00:00 2 | 1 | 2 | 2011-02-01 00:00:00 3 | 1 | 2 | 2011-03-01 00:00:00 4 | 1 | 3 | 2011-01-01 00:00:00 5 | 1 | 4 | 2011-01-01 00:00:00 6 | 1 | 3 | 2011-01-01 13:00:00 7 | 2 | 11 | 2011-06-06 11:23:42 (7 rows) 

Внутренний запрос дает вам следующее:

 => select id, rank() over (partition by thread_id order by created_at desc) from posts where receiver_id = 1; id | rank ----+------ 3 | 1 2 | 2 1 | 3 6 | 1 4 | 2 5 | 1 (6 rows) 

И затем мы обертываем внешний запрос вокруг этого, чтобы очистить только верхние совпадения:

 => select id from ( select id, rank() over (partition by thread_id order by created_at desc) from posts where receiver_id = 1 ) as dt where rank = 1; id ---- 3 6 5 (3 rows) 

Поэтому добавьте дополнительные столбцы, которые вы хотите, и заверните все в Post.find_by_sql и все готово.