Refactoring an Ecto query

Questions / Help
1

I have an Ecto query that I’d like to refactor to be more reusable and easier to read.

Here is this monstrosity:

def fuzzy_query_search(query, value, threshold) do
    from [p: p, u: u] in query,
      where:
        fragment(
          "levenshtein(?, ?)",
          p.title,
          ^value
        ) <= ^threshold or
        ilike(p.title, ^"%#{value}%") or
        fragment(
          "levenshtein(?, ?)",
          p.description_a,
          ^value
        ) <= ^threshold or
        ilike(p.description_a, ^"%#{value}%") or
        fragment(
          "levenshtein(?, ?)",
          u.name,
          ^value
        ) <= ^threshold or
        ilike(u.name, ^"%#{value}%")
  end

There is really 3 pieces here. The search on the title, description_a, and the name. I was trying to make this query look something like this:

def fuzzy_query_search(query, value, threshold) do
  query |> fuzzy_title_search(value, threshold)
        |> fuzzy_description_search(value, threshold)
        |> fuzzy_name_search(value, threshold)
end

But I couldn’t find a way to string the OR together through the function calls. Does anyone have thoughts on how to reduce the redundancy and make this thing easier to reason about?

2

You might take a look at or_where to see if that helps compose the query you’re looking for.

1 Like
3

Oh interesting, I think that is something I can really use here.

4

You could extract the common parts of that chain to a macro; here’s an example of doing that to create a cleaner way to write a range inclusion in Postgresql:

  defmacro daterange_contains_date(daterange, date) do
    quote do
      fragment("?::date <@ ?::daterange", unquote(date), unquote(daterange))
    end
  end

used in Ecto queries like:

from(things in Things,
where: daterange_contains_date(things.active_dates, ^today)
)

in your case it would wind up looking something like:

from [p: p, u: u] in query,
  where:
    complicated_thing(p.title, ^value, ^threshold) or
    complicated_thing(p.description_a, ^value, ^threshold) or
    complicated_thing(u.name, ^value, ^threshold)
3 Likes
5

Having some fun with my library :grin: that’s how it would look like:

query
|> QueryBuilder.where(
     [post: :user],
     &levenshtein_underthreshold_condition(:title, value, threshold, &1),
     or: {:title, :contains, value, case: :i},
     or: &levenshtein_underthreshold_condition(:description_a, value, threshold, &1),
     or: {:description_a, :contains, value, case: :i},
     or: &levenshtein_underthreshold_condition(:name@user, value, threshold, &1),
     or: {:name@user, :contains, value, case: :i}
   )

def levenshtein_underthreshold_condition(field, value, threshold, get_binding_fun) do
  {field, binding} = get_binding_fun.(field)
  Ecto.Query.dynamic([{^binding, x}], fragment("levenshtein(?, ?)", field(x, ^field), ^value) <= ^threshold)
end
1 Like