Composing an Ecto [v1] query with a dynamic amount of where filters

Question

This is a theoretical question but assume the data structure cannot change and that this is definitely how we want to do the query. This question is more to get a better understanding of how where filters would be composed dynamically rather than actually wanting to get the results from a query like this.

Imagine a database with a Car table in which each car has a manufacturer_id column that contains an ID such as "BD324" or "GM512", with "BD" & "GM" considered as prefixes.

We need to do a lookup on the car table such that cars get returned when their manufacturer_id prefix matches a given set of prefixes. So, given a list of prefixes:

prefixes = ["BD", "GM", "EX", "RD", "DE"]

..we'd want to return all cars that have a manufacturer_id that starts with any of those listed. i.e (LIKE x OR LIKE y OR LIKE z).

The following Elixir/Ecto code would search for one prefix:

search_prefix = Enum.at(prefixes, 0) <> "%"
from c in Car, where: like(c.manufacturer_id, ^search_prefix)

How would we go about building up the where clause based upon the prefixes list?

Answer 1

Ecto seems to be lacking a simple way to join dynamic parts of a query with OR, but I may have missed something. Please correct me if I am wrong.

However, you could use an equivalent ANY query, which I claim is both easier to read and simpler to build:

SELECT * from cars
 WHERE manufacturer_id LIKE ANY(ARRAY['BD%', 'GM%', 'EX%', 'RD%', 'DE%']);

To create this kind of query with Ecto, you can use a fragment:

prefixes_like = prefixes |> Enum.map(&"#{&1}%")
from c in Car,
  where: fragment("? LIKE ANY(?)", c.manufacturer_id, ^prefixes_like),
  select: c

Answer 2

You can think of composing query as a data transformation:

prefixes -> query with multiple where conditions

To do this you need to reduce one data structure to another, and Ecto 2 provides or_where which fits your need.

Below is an example:

def filter_by_prefixes(query, prefixes) do
  Enum.reduce prefixes, query, fn prefix, query ->
    filter_by_prefix(query, prefix)
  end
end

def filter_by_prefix(query, prefix) do
  or_where(query, [c], like(c.manufacturer_id, ^"#{prefix}%"))
end

# Then build the query
Car |> filter_by_prefixes(prefixes)