Where clause on a joined table using dynamic / composable queries

Questions / Help
1

I have the following query defined:

  defp active_events_query() do
    query =
      from product in Product,
        inner_join: venue in assoc(product, :venue),
    query
  end

Now for different situations, I have to add different where clauses and other join clauses to this query, so I expect to do this:

  def get_events(slug) do
    active_events_query()
    |> where([product], product.venue.slug == ^slug)
    |> Repo.all()
  end

However, the above fails saying:

** (Ecto.Query.CompileError) cannot fetch field `slug` from `product.venue`. Can only fetch fields from:

  * sources, such as `p` in `from p in Post`
  * named bindings, such as `as(:post)` in `from Post, as: :post`
  * parent named bindings, such as `parent_as(:post)` in a subquery

How do I add on another where clause to the query returned from active_events_query() where the condition being compared is a field in one of the earlier joined tables?

2

The binding argument can take a list:

query = from(product in Product, join: venue in assoc(product, :venue))

query
|> where([_product, venue], venue.slug == ^slug)
|> Repo.all()

Sometimes named bindings can help here so it’s a little clearer what binding you’ve matched on. (Although it can be a challenge to get the order right regardless.)

3

That worked! Thank you. I have a question though. Using this approach I am now dependent on knowing the order of the joins in the original query. If the original query changes by someone adding another join in the wrong place, then my query will break. Is there a way around t

4

@Arsenalist If order is an issue then I think named bindings (linked above) are they way to go:

query =
  from(
    product in Product,
    as: :product,
    join: venue in assoc(product, :venue),
    as: :venue
  )

query
|> where([venue: venue], venue.slug == ^slug)
|> Repo.all()

This will mean that you need the modules which define functions like get_events/1 to know which named binding they want. There are some helpers for this like has_named_binding?/2, but it can still be somewhat of an organizational challenge.

1 Like