How to apply join in Ecto using assoc after filtering records on the "right" table

Questions / Help
1

I am trying to retrieve all Products with active Variants. I only want the active variants to be included in product.variants. To do this I want to apply a join not only on the product IDs but also filter the active variants before applying the join. This is what I have tried (applying the where in both places).

      from product in Product,
        inner_join: variants in assoc(product, :variants),
        on: variants.active == true,
        where: variants.active == true

The result of the above is all products that have at least one active variant with product.variants including all variants (active or not).

How can I write this query so that only active variants are part of product.variants?

2

You didn’t show how you preloaded variants, but what you described is the expected result for default method of preloading the :variants association.

You have a couple options, you could use the joined table for the preload: preload: [variants: variants]. Alternatively you can alter the preload query for variants to only return active ones, see Ecto.Query — Ecto v3.11.2.

Aside: your where clause is redundant

1 Like
3

Oh thank you! I was reloading using preload: [:variants] and changing it to preload: [variants: variants] worked.

Thank you so much. This is such a helpful community and has made my Elixir journey much easier. Thank you again!