Esqueleto/raw SQL - сортировка запроса по результату сортировки в другой таблице?

Я на самом деле немного новичок в том, как работает SQL - я всегда позволяю своему ORM обрабатывать все для меня. Но в данном случае Persistent не предоставляет такую ​​функциональность, поэтому я не знаю, что делать.

У меня есть отношения многие ко многим:

+------------+
| Entries    |
+----+-------+
| id | date  |
+----+-------+
| 1  | Jan 1 |
+----+-------+
| 2  | Jan 2 |
+----+-------+
| 3  | Jan 3 |
+----+-------+

+------------+
| Tags       |
+------------+
| id | label |
+----+-------+
| 1  | Apple |
+----+-------+
| 2  | Boat  |
+----+-------+
| 3  | Car   |
+----+-------+

+-------------------+
| EntryTags         |
+----------+--------+
| entry_id | tag_id |
+----------+--------+
| 1        | 1      |
+----------+--------+
| 1        | 2      |
+----------+--------+
| 2        | 2      |
+----------+--------+
| 3        | 3      |
+----------+--------+

И я хочу отсортировать теги в первую очередь по дате их последней записи (по убыванию), а затем по метке (по возрастанию).

Тег CarПоследняя запись - 3 января, поэтому она идет первой. Тег Appleсамая последняя запись 2 января, но и тег Boat, Тем не менее, этикетка Apple предшествует лейблу Boat в алфавитном порядке, так Apple 2-й и Boat является третьим:

returns:
1. Tag w/ id 3
2. Tag w/ id 1
3. Tag w/ id 2

Благодаря моим исследованиям я понял, что для этого мне нужна какая-то комбинация соединений. Однако до сих пор я нашел только предложения для отношений один-ко-многим (сортировка тем в ветке по их последнему сообщению), и я думаю, что я их понимаю, но ни одно из них, включающее эти трехсторонние объединения для многих ко многим. связи.

В качестве возможного ответа я включил raw sql, потому что думаю, что на самом деле просто спрашиваю, как это сделать, и хотя я использую Esqueleto для своих привязок SQL, я думаю, что как только я пойму SQL, перевод на эскелето будет простым. Я использую postgresql в качестве моего бэкэнда, но я бы предпочел не использовать специфичные для postgres вещи, потому что мои привязки предназначены для общих бэкэндов.

Кто-нибудь есть идеи, где я могу начать? На какие объединения я должен смотреть и как отсортировать последние записи?

Источник

2 ответа

Решение

Другое решение:

select t.id as tag_id, t.label as tag, max(date) as date from Tags t
join EntryTags et on t.id=et.tag_id
join Entries e on e.id=et.entry_id
group by t.label,t.id
order by date desc,tag

который возвращает:

tag_id  tag    date   
------  -----  -----  
1       Apple  jan 3  
3       Car    jan 3  
2       Boat   jan 2

(По вашим данным, последняя запись Apple - 3 января, а не 2 января.)

Объединения в Postgres - это безбожие "внутренние объединения". Если у вас могут быть теги без записей, вы можете поменять их на левые.

Источник

SQL Fiddle

Ваши образцы данных не соответствуют тексту. Таблицы записей в таблице должны быть

(1, 1),
(1, 2),
(2, 2),
(2, 1),
(3, 3);

select id, label
from (
    select distinct on (t.id) e.date, t.label, t.id
    from
        entries e
        inner join
        entrytags et on et.entry_id = e.id
        inner join
        tags t on t.id = et.tag_id
    order by t.id, e.date desc, t.label
) s
order by "date" desc, label
Источник
Другие вопросы по тегам