Ecto Query: Count/1 of left_joined value multiplied by outer_join value count

Question

I have an items postgres table that has_many bookmarks and has_many notes. I am querying these with {:ecto_sql, "~> 3.7"}.

I want a query that returns all finished items that have notes, and I want that query to also count that item's bookmarks.

When I left_join a note's bookmarks and select_merge count(bookmarks), I get the proper count, but when I add an inner_join notes to the item, the count of bookmarks is multiplied by the number of notes, e.g. if an item has 2 bookmarks and 4 notes, bookmark_count will be 8 when it should be 2.

Here is my funky ecto query:

from item in Item,
  where: item.finished == true,
  left_join: bookmark in assoc(item, :bookmarks),
  on: bookmark.item_id == item.id and bookmark.deleted == false,
  select_merge: %{bookmark_count: count(bookmark)},
  inner_join: note in assoc(item, :notes),
  on: note.accepted == true

Many thanks in advance for feedback/guidance!

Answer 1

Basically: aggregate the N-side before joining to avoid multiplying rows from the main table. Faster, too. See:

• Two SQL LEFT JOINS produce incorrect result

Use a semi-join for notes with EXISTS to only verify the existence of a related qualifying row. This also never multiplies rows.

This query should implement your objective:

SELECT i.*, COALESCE(b.ct, 0) AS bookmark_count
FROM   items i
LEFT   JOIN (
   SELECT b.item_id AS id, count(*) AS ct
   FROM   bookmarks b
   WHERE  NOT b.deleted
   GROUP  BY 1
   ) b USING (id)
WHERE  i.finished
AND    EXISTS (
   SELECT FROM notes n
   WHERE  n.item_id = i.id
   AND    n.accepted   
   );

I slipped in a couple other minor improvements.