Nested subqueries with Ecto

Questions / Help
1

I have a User that has one Library which has many Subjects which has many Books.

Given the user_id I wish to select the user’s books.

First attempt

%Library{subjects: subjects} = get_library_by_user_id(user_id) |> Repo.preload([:subjects])
subjects = subjects |> Enum.map(fn sub -> sub.id end)
Books |> where([bk], bk.subject_id in ^subjects) |> Repo.all

Second Attempt (cleaner but now I have to wrangle the Books out of the User struct)

Repo.all(from lib in Library, where: lib.user_id == ^user_id, preload: [{:subjects, :books}])

Third attempt (doesn’t work):

Repo.all(from bk in Book, where: bk.subject_id in subquery(
  from subj in Subject, where: subj.library_id == subquery(from lib in Library, where: user_id == ^user_id, select: lib.id),
select: subj.id))

This is how I’d write it in pure SQL.

Does anybody know either A: How to get the last query working? B: A better way of doing this?

TIA

2

Based on the description I’d probably just do something like:

query =
  from bk in Book,
    join: subj in Subject, on: subj.id == bk.subject_id,
    join: lib in Library, on: lib.id == subj.library and lib.user_id == ^user_id,
    select: bk
Repo.all(query)

No need for subqueries on something like this yes?

2 Likes
3

That works. Awesome! Thank you.

1 Like