Please how can I factorize a condition with multiple alternative conditions in Ecto.Query?

Questions / Help
, ,
1

Please see the below examples:

# Approach 1
Query
|> where([...], cond_a and cond_b)
|> or_where([...], cond_a and cond_c)
|> or_where([...], cond_a and cond_d)
|> or_where([...], cond_a and cond_e)

# Approach 2
Query
|> where([...], cond_a)
|> where([...], cond_b or cond_c or cond_d or cond_e)

Isn’t there another way to write this query without repeating cond_a apart from the approach2?

Something like:

Query
|> where_always([..], cond_a)
|> or_where([...], cond_b)
|> or_where([...], cond_c)
|> or_where([...], cond_d)
|> or_where([...], cond_e)

Edit: where_always of course is purely imaginary and means the condition should be met first and always then we can consider the or_where conditions.

2 Likes
2

Generally I suggest using the ˋdynamicˋ macro wherever you‘re in need of more complex condition combinations.

2 Likes
3

Why not this:

query
|> where([...], cond_a and (cond_b or cond_c or cond_d or cond_e))
1 Like
4

Yes that works too.
But in fact I was composing some query with different reusable functions.

Here is an example of what I have.

  def search_users(term, params) do
    User
    |> join_search_assocs
    |> searchable_client()
    |> search_matching(term)
    |> order_search_results(term)
    |> preload([u, cl, ...], client: cl)
    |> Repo.paginate(params)
  end

searchable_client for example is a where pipe that ensures user has :client role and an avatar. I use it in several places. Then if i want to add or remove conditions for viewable client profiles I would just rewrite that function and it will be reflected everywhere…

search_matching does something like

query
|> where([u, ..., p], ilike(u.name, ^term) or ilike(p.title, ^term))

but in more complex way since I’m searching through many joined tables and I’m also using PostgreSQL ts_vector feature.

5

Then I absolutely would have composed a dynamic where instead (using dynamic) and then put that onto the final query via where as normal.

1 Like