Ecto join preloading the wrong nested row

Questions / Help
,
1

I’m having trouble with a simple join/preload query that is returning incorrectly.

I have three tables: Books, Editions, and RegistryBooks. RegistryBooks has two rows

%{book_id: 1, edition_id: 1}
%{book_id: 1, edition_id: 2}

I want to build a query that fetches both rows and preloads both the book and the edition.

Market.RegistryBook
|> join(:inner, [rb], b in Library.Book, on: b.id == rb.book_id)
|> join(:inner, [rb, b], e in Library.BookEdition, on: e.id == rb.edition_id)
|> preload([rb, b, e], book: {b, edition: e})
|> Repo.all()

Two rows are being returned, but the issue is that even though the ReaderBook has the correct edition_id, the nested edition is wrong. It always matches whatever is the first result. So in this case both preloaded nested editions are id: 1.

%{book_id: 1, edition_id: 1, book: %{edition: %{id: 1}}}
%{book_id: 1, edition_id: 2, book: %{edition: %{id: 1}}}

I’ve run the same query on the database directly and it returns the correctly joined edition, so maybe this is an issue with Ecto? What am I missing here?

2

Is your second join meant to be joining on the book instead of the registry book? b.id instead of rb.id.

One debugging tip: look into how you can use the Ecto SQL adapter to see the SQL generated from a query.

3

rb.id is what I want. Thanks for the tip on the Ecto SQL adapter. I’ll look at the generated SQL to see how it differs from what I wrote. One thing that is working is if I replace the preload with a select

|> select([rb, b, e], %{rb | book: %{b | edition: e}})

The select returns the correct results.

4

This seems like you want RegistryBook structs with the same book_id value to get different Book structs. That’s the opposite of what preload promises…

5

Ah yes, that does make sense. I want RegistryBook structs with the same Book struct except, that the edition field of the Book struct should have a different BookEdition struct.

BookEdition has a belongs_to relationship with Book.
Book has a has_many relationship with BookEdition.