So I’m a bit confused and would really appreciate your help.

I have a query like this:

query = from(
       c in App.Card, 
       left_join: comments in assoc(c, :comments)
   )
App.Repo.get(query, 557)

It returns the card I’m looking for but with no comments preloaded. So I can’t access them even though I’ve joined the tables. So I go ahead and preload the comments

query = from(
       c in App.Card, 
       left_join: comments in assoc(c, :comments),
       preload: [:comments]
   )
App.Repo.get(query, 557)

And it now preloads and I can access the comments.

What worries me is the following:

The first query resulted in the following SQL:

[debug] QUERY OK source="cards" db=0.7ms
SELECT c0."id", c0."title", c0."description", c0."position", c0."board_column_id", c0."author_id", c0."inserted_at", c0."updated_at" FROM "cards" AS c0 LEFT OUTER JOIN "card_comments" AS c1 ON c1."card_id" = c0."id" WHERE (c0."id" = $1) [5556] 

But the second one resulted in an additional SELECT statement

[debug] QUERY OK source="cards" db=0.9ms
SELECT c0."id", c0."title", c0."description", c0."position", c0."board_column_id", c0."author_id", c0."inserted_at", c0."updated_at" FROM "cards" AS c0 LEFT OUTER JOIN "card_comments" AS c1 ON c1."card_id" = c0."id" WHERE (c0."id" = $1) [556]
[debug] QUERY OK source="card_comments" db=0.5ms
SELECT c0."id", c0."content", c0."remote_id", c0."author_id", c0."card_id", c0."inserted_at", c0."updated_at", c0."card_id" FROM "card_comments" AS c0 WHERE (c0."card_id" = $1) ORDER BY c0."card_id" [556]

Is this expected behavior? Is this something I should be concerned about? Because this is just a small example from a larger query where I do multiple joins and preloads (resulting in many SELECT statements). It seems like there’s an N+1 problem here.

Another question is concerning the joins and preloads when having multiple comments. The query above will throw an error because it will return x number of rows where x is the number of comments associated with the card. This makes sense considering how join works but I would assume that Ecto would abstract that away? Or am I just to spoiled by Rails? Perhaps I’m doing something very wrong here. Any help is much appreciated.

Thanks everyone