Elixir Ecto named binding for child table is not possible

Questions / Help
1

I have two tables Tab 1 and Tab 2.
Tab 2 belongs to Tab 1 and Tab 1 has many tab 2 records.

Based on the examples in the documentation so far, applying limit on Tab 1 affects Tab 2 because introducing a join makes the query a single query. However, I want to apply limit to Tab 1 and still retrieve all child records in Tab 2 for Tab 1 using named binding.
Thanks in advance for any assistance.

2

I think you can take some inspiration from this older post:

1 Like
3

The easiest option is to preload Tab 2

4

Ecto does not allow named binding for Tab 2 when you preload, unless it’s a join, and that affects the query result

5

thanks @thiagomajesk. I am taking a close look at the post.

6

I meant preload in its simplest form, which runs in a separate query:

from(Tab1, preload: :tab_2, where: ...)
7

Please let me check on this one too

8

You can use naive preload but keep in mind that it will add another round-trip.

You could instead use a subquery, like:

authors_query = from Author, limit: 10

books_query =
  from author in subquery(authors_query),
    join: b in assoc(author, :books),
    select: author,
    preload: [books: b]
1 Like
9

when I use this approach, a single query is formed that is sent to the database, and is therefore, affected by the limit.
What I wanted was that the limit should apply to just the Author.
Example. if Author limit is 1, I still want to see all books under Author.
When you use the join approach, only 1 book is returned instead of all the books being returned.

10

The limit will only apply to the subquery not to the query as a whole.

11

How do I make it apply to the main query (parent) and not the subquery?

12

You don’t want that, because the limit on the main query will affect rows of your results directly. Only in subqueries or CTEs can you limit a resultset and then do further work joining it to more data.

13

oh…wow.
Okay.
Thanks @LostKobrakai