Nested preload with condition

Questions / Help
1

My schema looks something like this:

  • product belongs to category
  • product belongs to branch (= a shop)
  • category has many products
  • branch has many products
  • branch has many categorys through product

The respective Ecto schema is set up correctly. I want to load all branches together with their categories, which in turn should include all products of that category which are available in that branch.

I defined a with_categories helper function in my Branch module:

def with_categories(query) do
    from q in query,
      join: c in assoc(q, :categories),
      join: p in assoc(c, :products),
      preload: [categories: {c, products: p}]
end

# usage:
Branch |> with_categories |> Repo.all

This gives me all branches with correct categories (e.g. a branch that doesn’t have products of a certain category will not have that category either), but the nested assocs category.products contain all products in that category, regardless of branch.

How can I apply the condition p.branch_id == q.id to the nested preload :products?

1 Like
2

Because you joining the products to the categories, not the branch. Try joining the products to the branch instead…

3 
# Branch.ex
def with_categories(query) do
    from q in query,
      join: c in assoc(q, :categories),
      join: p in assoc(q, :products), on: p.category_id == c.id,
      preload: [categories: {c, products: p}]
end

This gives me the same result… I suspect that preloading does not respect the conditions of the original query.

The Ecto docs say that preload queries are possible, however I am currently not able (or do not understand how) to use the bound parameters q.id and c (q == Branch, c == Category) inside of a preload query.

I would like to use something like this as my preload query (this will not work because c, p and q are not bound to their values inside of the parent from context):

preload: [
  categories: {
    c,
    products: ^from(p, where: p.category_id == c.id, where: p.branch_id == q.id)
  }
]

Btw, I’m using Postgres if that matters.

4

Could anyone from the Ecto core team have a look at this? @michalmuskala? This might be a bug with preloading…

5

This looks entirely correct to me. You’re asking to preload all categories and for each category to preload all products. Nowhere in the query, there’s a constraint that a product should be of a particular branch.

You probably wanted something like:

def with_categories(query) do
    from q in query,
      join: c in assoc(q, :categories),
      join: p in assoc(q, :products), on: p.branch_id == q.id,
      preload: [categories: {c, products: p}]
end
6

Thanks for chiming in! I just tried adding branch_id to the constraints and now I get a list of all products (→ the full unconstrained set) in every category in every branch :smiley:

I set up a repo so you can reproduce this thing: https://github.com/dlindenkreuz/ecto_nested_preload_test

It would be great if you could give this a quick look, I tried so many variants in the query and this problem reeeaally bugs me :tired_face:

7

it does look strange, thought I solved it but no.

this one gives expected result:
NestedPreloadTest.Repo.all(from b in NestedPreloadTest.Shop.Branch, preload: [products: :category])

but the one you’re trying doesnt:
NestedPreloadTest.Repo.all(from b in NestedPreloadTest.Shop.Branch, preload: [categories: :products])

suppose it’s the missing branch constraints when “categories” has_many products… so some special query might be needed…

8

^ solution in 2020