Ecto preload causing multiple SELECT queries

Questions / Help
1

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? :slight_smile: Perhaps I’m doing something very wrong here. Any help is much appreciated.

Thanks everyone :slight_smile:

2 Likes
2

I think that there is a little mistake in your query.

In your example you write:

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

This is joining the comments table and pointing it in the comments variable. In the preload you should use this comments that you have already loaded. Try this:

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

The Ecto docs contain more information about how to use preloads.

10 Likes
3

@belaustegui You are awesome. Thank you so much. This actually cleared up my concerns. Have a great day :smile:

3 Likes
4

@jdj_dk Awesome question too, :grin:

1 Like