Independent aggregation of related entities, while also sorting by related (in a single statement) I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named authorships.
My goal is to query for some subset of the books, and also get the set of related authorships, and the set (i.e. without duplicates) of related authors, each in some particular order. In essence I want to retain the normalized/separated structure of the records, I don't want to denormalize in any way (just sort).
Typically I think you'd do this using multiple statements, using a function or external code to provide the IDs to IN expressions or similar. However I've been able to use the following pattern in PostgreSQL to do it in a single statement:
WITH matched_books AS (
  SELECT id, title FROM books
  -- Could be any criteria:
  WHERE title LIKE 'The %'
),
related_authorships AS (
  SELECT authorships.id, book_id, author_id
  FROM authorships
  JOIN matched_books ON book_id = matched_books.id
),
related_authors AS (
  SELECT id, name
  FROM authors
  -- Could also use DISTINCT and do a join here, but I understand EXISTS is typically better for performance:
  WHERE EXISTS (SELECT 1 FROM related_authorships WHERE author_id = authors.id)
)
SELECT
  -- Scalar subqueries that each return a single JSON array of objects:
  -- JSON is completely fine for my purposes, but could also use array_agg.
  (SELECT json_agg(matched_books.* ORDER BY title) FROM matched_books) books,
  (SELECT json_agg(related_authorships.* ORDER BY id) FROM related_authorships) authorships,
  (SELECT json_agg(related_authors.* ORDER BY name) FROM related_authors) authors;

(Side note: In previous attempts I had used LEFT JOINs at the top level and json_agg(DISTINCT ...), but that left me unable to use ORDER BY meaningfully, and seemed messier/worse for performance.)
While this approach almost works great, I now want to order the books by information stored in their related authors and/or authorships. As an obvious example, let's say I want them to be sorted by the name of their author, or if any have multiple authors, use a column from authorships (it can just be the lowest integer id in this case) to determine which author should be used.
I can't think of an approach that will allow this while still returning the sets independently, at least not without some duplication of operations. How would you solve this?
I azm not sure do I undersgtand you correctly, but probably you need to group additional field from authors to have only one value for ORDER. It might be count of athours, or min / max for name or so:
WITH books AS (
  SELECT 1 AS id, 'The 1' AS title
   UNION ALL
  SELECT 2, 'The 2'
   UNION ALL
  SELECT 3, '3'
), authorships AS (
  SELECT 1 AS id, 1 AS book_id, 1 AS author_id
   UNION ALL
  SELECT 2, 1, 2
   UNION ALL
  SELECT 3, 1, 3
   UNION ALL
  SELECT 4, 2, 1
   UNION ALL
  SELECT 5, 3, 1
), authors AS (
  SELECT 1 AS id, 'name1' AS name
   UNION ALL
  SELECT 2, 'name2'
   UNION ALL
  SELECT 3, 'name3'
   UNION ALL
  SELECT 4, 'name4'
), filtered AS (
  SELECT book_id, title, ba.id, author_id, name
    FROM books AS b
    JOIN authorships AS ba ON ba.book_id = b.id
    JOIN authors AS a ON a.id = ba.author_id WHERE title LIKE 'The %'
) 
SELECT (
         SELECT json_agg(b.* ORDER BY count)
           FROM (
                  SELECT book_id AS id, title, count(name)
                    FROM filtered AS f GROUP BY 1,2
                ) AS f
           JOIN LATERAL (SELECT id, title) AS b ON true)
       ) AS book 

           json_agg           
-----------------------------
 [{&quot;id&quot;:2,&quot;title&quot;:&quot;The 2&quot;}, +
  {&quot;id&quot;:1,&quot;title&quot;:&quot;The 1&quot;}]
(1 row)