Dynamic Conditionals with Ecto

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 query.

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