haskell - Esqueleto/raw SQL -- Sorting a query by the result of a sort on another table? -

i little new how sql works -- i've let orm's handle me. in case persistent doesn't expose kind of functionality i'm @ lost @ do.

i have many-to-many relationship:

+------------+ | 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      | +----------+--------+ 

and want sort tags date of recent entry (descending), secondarily label (ascending).

tag car's recent entry on jan 3rd, goes first. tag apple's recent entry january 2nd, tag boat. however, label apple comes before label boat alphabetically, apple 2nd , boat 3rd:

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

through research i've figured out need kind of combination of joins this. far i've found soutions one-to-many relations (sorting topics in thread recent post), , think understand them, none involving these three-way joins many-to-many relations.

i'm including raw sql possible answer because think i'm asking sql way it, , though i'm using esqueleto sql bindings, think once understand sql, translation esqueleto straightforward. i'm using postgresql backend, i'd rather not use postgres-specific things because bindings general backends.

anyone have idea can start? kind of joins should looking @ , how sort through recent entry?

another solution:

select t.id tag_id, t.label tag, max(date) date tags t join entrytags et on t.id=et.tag_id join entries e on e.id=et.entry_id group t.label,t.id order date desc,tag 

which returns:

tag_id  tag    date    ------  -----  -----   1       apple  jan 3   3       car    jan 3   2       boat   jan 2   

(in data, apple's recent entry january 3, not january 2.)

joins in postgres implicity "inner joins". if may have tags without entries, want change joins left joins.