Recently, I needed to take a user's query and match it against multiple fields, and realized I needed to learn more about the
dynamic clause for
Ecto.Query. The situation arises when you want a single powerful search field that matches all or part of the query string on multiple database columns. Generally we'd use a full text tokenizing search index like Elasticsearch for something like this, but in this case we chose to work with a dynamic query in order to build out the feature quickly without the need for more infrastructure. The result is a query that may not perform well for a very long query string, but is much faster to prototype than a full text engine. This post may be helpful if you find yourself in a similar situation.
def filter(value, query) do # value is the string entered by the user # query is the existing database query with prior scopes applied values = value |> String.split(~r/ +/, trim: true) # split on and remove all extra whitespace |> Enum.map(fn value -> "%" <> String.replace(value, ~r/[\b\W]+/, "%") <> "%" # replace non characters with wildcard characters end) conditions = values |> Enum.reduce(false, fn v, acc_query -> dynamic( [t], ilike(t.field1, ^v) or ilike(t.field2, ^v) or ilike(t.field3, ^v) or ^acc_query ) end) query |> where(^conditions) end
Taking the user input as
value and the scoped
query as inputs, the result will be the
query with the additional
where clause of our dynamic conditional.
We start by breaking the
value into small pieces with the
% wildcard to match out
ilike conditions in the PostgreSQL query. Once we have the list of values we want to match on, we can
reduce over them, adding the
or of all our
ilike conditions on to the accumulating
acc_query with each pass. Finally, we take all those conditions and pin them to the original
query, so they become a parenthesized
and clause on the original
You can clearly see that for a really long input with lots of special characters and spaces, this will not perform well, and in cases covering a lot of fields, it can also be very slow. Some of this can be mitigated with indexes on the appropriate columns in PostgreSQL, but ultimately this is likely to be a stop-gap until you include a full text engine. Hopefully this helps you build something out quickly so you can get the most important part of product development, feedback!
Header photo by chuttersnap on Unsplash