0

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?

2 Answers 2

0

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           
-----------------------------
 [{"id":2,"title":"The 2"}, +
  {"id":1,"title":"The 1"}]
(1 row)
0

So after much trial-and-error I was able to come up with two approaches that work at acceptable speeds.

With the approach used in the question example, I determined that there was unfortunately no way around repeating the joins:

WITH matched_books AS (
  SELECT id, title FROM books
  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
  WHERE EXISTS (SELECT 1 FROM related_authorships WHERE author_id = authors.id)
)
SELECT
  (SELECT json_agg(matched_books.* ORDER BY first_author_name)
    FROM matched_books
    LEFT JOIN (
      SELECT DISTINCT ON (book_id) book_id, name AS first_author_name
      FROM related_authorships
      LEFT JOIN related_authors ON author_id = related_authors.id
      ORDER BY book_id, related_authorships.id
    ) sub ON book_id = matched_books.id
  ) 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;

EXPLAIN plan looks like this:

Result  (cost=1344.93..1344.94 rows=1 width=96)
  CTE matched_books
    ->  Seq Scan on books  (cost=0.00..157.94 rows=1169 width=23)
          Filter: (title ~~ 'The %'::text)
  CTE related_authorships
    ->  Hash Join  (cost=128.05..173.70 rows=1204 width=12)
          Hash Cond: (matched_books.id = authorships.book_id)
          ->  CTE Scan on matched_books  (cost=0.00..23.38 rows=1169 width=4)
          ->  Hash  (cost=74.69..74.69 rows=4269 width=12)
                ->  Seq Scan on authorships  (cost=0.00..74.69 rows=4269 width=12)
  CTE related_authors
    ->  Hash Join  (cost=31.59..138.06 rows=1204 width=18)
          Hash Cond: (authors.id = related_authorships.author_id)
          ->  Seq Scan on authors  (cost=0.00..85.99 rows=2699 width=18)
          ->  Hash  (cost=29.09..29.09 rows=200 width=4)
                ->  HashAggregate  (cost=27.09..29.09 rows=200 width=4)
                      Group Key: related_authorships.author_id
                      ->  CTE Scan on related_authorships  (cost=0.00..24.08 rows=1204 width=4)
  InitPlan 4 (returns $3)
    ->  Aggregate  (cost=821.01..821.02 rows=1 width=32)
          ->  Hash Left Join  (cost=791.57..818.09 rows=1169 width=60)
                Hash Cond: (matched_books_1.id = sub.book_id)
                ->  CTE Scan on matched_books matched_books_1  (cost=0.00..23.38 rows=1169 width=32)
                ->  Hash  (cost=789.07..789.07 rows=200 width=36)
                      ->  Subquery Scan on sub  (cost=750.83..789.07 rows=200 width=36)
                            ->  Unique  (cost=750.83..787.07 rows=200 width=40)
                                  ->  Sort  (cost=750.83..768.95 rows=7248 width=40)
                                        Sort Key: related_authorships_1.book_id, related_authorships_1.id
                                        ->  Merge Left Join  (cost=171.37..286.11 rows=7248 width=40)
                                              Merge Cond: (related_authorships_1.author_id = related_authors.id)
                                              ->  Sort  (cost=85.69..88.70 rows=1204 width=12)
                                                    Sort Key: related_authorships_1.author_id
                                                    ->  CTE Scan on related_authorships related_authorships_1  (cost=0.00..24.08 rows=1204 width=12)
                                              ->  Sort  (cost=85.69..88.70 rows=1204 width=36)
                                                    Sort Key: related_authors.id
                                                    ->  CTE Scan on related_authors  (cost=0.00..24.08 rows=1204 width=36)
  InitPlan 5 (returns $4)
    ->  Aggregate  (cost=27.09..27.10 rows=1 width=32)
          ->  CTE Scan on related_authorships related_authorships_2  (cost=0.00..24.08 rows=1204 width=32)
  InitPlan 6 (returns $5)
    ->  Aggregate  (cost=27.09..27.10 rows=1 width=32)
          ->  CTE Scan on related_authors related_authors_1  (cost=0.00..24.08 rows=1204 width=88)

Question: Are there any obvious indexes or other optimizations I'm missing, other than one for the initial LIKE condition?

The second approach is to join everything first then extract each entity type afterwards, it's definitely a bit more awkward:

WITH joined AS (
  -- Use row/composite values to keep things separate
  SELECT books, authorships, authors
  FROM (SELECT id, title FROM books) books
  LEFT JOIN (SELECT id, book_id, author_id FROM authorships) authorships ON books.id = authorships.book_id
  LEFT JOIN (SELECT id, name FROM authors) authors ON authors.id = authorships.author_id
  WHERE title LIKE 'The %'
),
related_authorships AS (
  SELECT DISTINCT ON ((authorships).id) (authorships).*
  FROM joined
  WHERE (authorships).id IS NOT NULL
),
related_authors AS (
  SELECT DISTINCT ON ((authors).id) (authors).*
  FROM joined
  WHERE (authors).id IS NOT NULL
)
SELECT
  (SELECT json_agg(books ORDER BY first_author_name)
    FROM (
      SELECT DISTINCT ON ((books).id) books, (authors).name AS first_author_name
      FROM joined
      ORDER BY (books).id, (authorships).id
    ) sub
  ) 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;

I won't paste the query plan; the cost factor is lower than the first query, but in practice it takes slightly longer on average (I know I can make some micro-optimizations with this particular version but I left it like this for better clarity). Combined with the awkward parts, I prefer the first approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.