How do I use a dynamic comparison operator in Ecto?

[sirfitz]

Hi Everyone!

I’d really appreciate if I could get some help with this issue I ran into.

I’m trying to allow searching on various fields in a database, this is the situation:

1. A user wants to find all posts that were created by users who joined before October 2020
2. A user wants to find all posts that were created by users who joined after October 2020
3. A user wants to find all posts that were created by users who joined on October 5th 2020

normally I would write various versions of this query like so:

def before_date(query, date) do
 query
 |> where([posts, users], users.inserted_at < ^date))
end 
def after_date(query, date) do
 query
 |> where([posts, users], users.inserted_at > ^date))
end 
def on_date(query, date) do
 query
 |> where([posts, users], users.inserted_at == ^date))
end

Then to filter on the posts date themselves, I would need to write those queries over again and uses posts.inserted_at instead.

Therefore I’m looking for a way to dynamically compare items inside an Ecto Query.

I found this question: Create Ecto query with dynamic operators

but it didn’t compile as the compile complained that the query was not valid:

== Compilation error in file lib/mmsapi/liquid/search/filters.ex ==
** (CompileError) lib/mmsapi/liquid/search/filters.ex:338: invalid call operator(field(o, ^field_name), ^value)
    expanding macro: Liquid.Search.Filters.custom_where/4

so a slight modification and got to this, by replacing the variable operator with an actual operator:

defmacrop custom_where(t, f, v, :==) do
 {:==, [context: Elixir, import: Kernel],
  [
    {:field, [], [t, {:^, [], [f]}]},
    {:^, [], [v]}
   ]}
end

def compare_field(query, field_name, value, operator) do
  query
  |> where([o], ^custom_where(o, field_name, value, operator))
end

However with that I get this error:

== Compilation error in file lib/mmsapi/liquid/search/filters.ex ==
** (CompileError) lib/mmsapi/liquid/search/filters.ex:327: cannot use ^field_name outside of match clauses

This is the kind of code I’m trying to achieve:

 field_name = :inserted_at

 value = DateTime.utc_now()

 operator = :==

 query
 |> where([posts, users], custom_where(users, field_name, value, operator))

Or event better yet, so that I could use it for dynamic joins:

 join_name = :users

 field_name = :inserted_at

 value = DateTime.utc_now()

 operator = :==

 query
 |> where([posts, {join_name, u}], custom_where(u, field_name, value, operator))

Any assistance would be most appreciated, and thank you in advanced!

[fuelen]

operator in compare_field function must be given at compile time, not at runtime, so basically you have to generate multiple functions compare_field where operator is “hardcoded”.

  defmacrop custom_where(t, f, v, o) do
    {o, [context: Elixir, import: Kernel],
     [
       {:field, [], [t, {:^, [], [f]}]},
       {:^, [], [v]}
     ]}
  end

  def compare_field(query, field_name, value, :==) do
    query
    |> where([o], custom_where(o, field_name, value, :==))
  end

  def compare_field(query, field_name, value, :>=) do
    query
    |> where([o], custom_where(o, field_name, value, :>=))
  end

iex(7)> (from u in "users") |> compare_field(:name, "john", :==)
#Ecto.Query<from u0 in "users", where: u0.name == ^"john">
iex(8)> (from u in "users") |> compare_field(:age, 18, :>=)     
#Ecto.Query<from u0 in "users", where: u0.age >= ^18>

[Eiji]

To make it work we need a macro which generates where dynamically. However the problem of it is that an operator need to be passed explicitly i.e. not by variable as macro accepts AST and therefore pattern matching for variables does not works.

This can be solved by generating a function, so both pattern-matching in function head as well as value passed to macro are just unquoted atoms.

For example:

defmodule Example do
  defmacrop op_test(a, b, operator) do
    {operator, [context: Elixir, import: Kernel], [a, b]}
  end

  for op <- [:<, :==, :>] do
    def sample(a, b, unquote(op)) do
      op_test(a, b, unquote(op))
    end
  end
end

iex> Example.sample(2, 1, :<) 
false
iex> Example.sample(2, 1, :==)
false
iex> Example.sample(2, 1, :>)
true

Here goes an example script:

example.exs

Mix.install([:ecto])

defmodule Comment do
  use Ecto.Schema

  schema "comments" do
    belongs_to(:post, Post)
    field(:date, :naive_datetime)
  end
end

defmodule Post do
  use Ecto.Schema

  schema "posts" do
    field(:date, :naive_datetime)
    has_many(:comments, Comment)
  end
end

defmodule Example do
  import Ecto.Query

  defmacrop macro_filter(queryable, binding, field_name, operator, value) do
    {:where, [],
     [
       queryable,
       [
         {{:^, [], [binding]}, {:relation, [], Elixir}}
       ],
       {operator, [context: Elixir, import: Kernel],
        [
          {:field, [], [{:relation, [], Elixir}, {:^, [], [field_name]}]},
          {:^, [], [value]}
        ]}
     ]}
  end

  def sample(binding, field_name, operator, value) do
    Post
    |> from(as: :post)
    |> join_relation(binding)
    |> filter(binding, field_name, operator, value)
  end

  defp join_relation(queryable, :post), do: queryable

  defp join_relation(queryable, :comments) do
    join(queryable, :inner, [post: post], assoc(post, :comments), as: :comments)
  end

  defp filter(queryable, binding, field_name, :!=, nil) do
    where(queryable, [{^binding, relation}], not is_nil(field(relation, ^field_name)))
  end

  defp filter(queryable, binding, field_name, :==, nil) do
    where(queryable, [{^binding, relation}], is_nil(field(relation, ^field_name)))
  end

  for operator <- [:!=, :<, :<=, :==, :>, :>=, :ilike, :in, :like] do
    defp filter(queryable, binding, field_name, unquote(operator), value) do
      macro_filter(queryable, binding, field_name, unquote(operator), value)
    end
  end
end

:post |> Example.sample(:date, :!=, nil) |> IO.inspect()
:comments |> Example.sample(:date, :>=, NaiveDateTime.utc_now()) |> IO.inspect()

results

#Ecto.Query<from p0 in Post, as: :post, where: not(is_nil(p0.date))>
#Ecto.Query<from p0 in Post, as: :post, join: c1 in assoc(p0, :comments),
 as: :comments, where: c1.date >= ^~N[2021-01-30 22:22:51.317528]>

Note: nil values must be handled separately for security reasons:

nil comparison

nil comparison in filters, such as where and having, is forbidden and it will raise an error:

# Raises if age is nil
from u in User, where: u.age == ^age

This is done as a security measure to avoid attacks that attempt to traverse entries with nil columns. To check that value is nil, use is_nil/1 instead:

from u in User, where: is_nil(u.age)

Source: Ecto.Query — Ecto v3.11.1

Note: Mix.install/1 (new useful feature for writing scripts) is available since Elixir version 1.12.0 (currently in master branch):

Add Mix.install/2 (#10674)

Tip: Using code generation with for as before you can write an easy implementation of aliasing a human readable filters like :gt to ecto operator :>. You just need a simple keyword of aliases like: [eq: :==, gt: :>, lt: :<] and so on. Also you can do the same with relations.

There is similar implementation in ecto_shorts library:
ecto_shorts/lib/query_builder/schema.ex at main · MikaAK/ecto_shorts · GitHub

Have fun!

[sirfitz]

Thanks for the reply @fuelen, this gives me a better understanding of how things work

[sirfitz]

Thanks so much for your very detailed response and advice, this will definitely help me going forward.

I tested out the code with my project, all I needed to do was update the Ecto version to get it working, so I can now move forward with the refactoring

Thanks!

[rlopzc]

Hey! Amazing answer, now I understand a little bit more the AST and macro things but not quite

I’m trying to do something like those where functions but also add them the dynamic binding params if a named binding exists (the same module name in atom)

For example, with your solution you can only add filters where you specify the binding, even if the filter works on the same module, ie. :post |> Example.sample(:date, :!=, nil) |> IO.inspect().

I want to achieve where clauses where if there is a named binding (through a join) works and also if there isn’t

defmodule Winner do
  schema ...

  ## Queries
  def with_secret(query \\ __MODULE__, secret) do
    custom_where(query, :secret, :==, secret)
  end

Examples

  query_with_join  = 
    Query.join(Ticket, :inner, [t], w in Winner, as: :winner, on: t.id == w.ticket_id)
    |> Winner.with_secret("123")

  single_winner_query = Winner.with_secret("123")

Both should work, internally the same custom_where fn checks if there is a Query.has_named_binding?(query, :winner) and uses the correct binding params. ([{^module, q}] or [winner: q] or [{:winner, q}] vs [q])

I was algo thinking that getting the __MODULE__ name dynamically so any module that uses the custom_where benefits.

Something like

  defp module_name() do
    unquote(__MODULE__) # Pardon my lack of knowlege on Macros
    |> Module.split()
    |> List.last()
    |> String.downcase()
    |> String.to_atom()
  end

  iex> module_name(Winner) => :winner

How do you think I can ahieve this?

Thank you for your time and answer

[Eiji]

You do not need a macro for this. Let’s take a look at simplest example:

Mix.install([:ecto])                                                                 

alias Ecto.Query                                                                    
require Query

query = Query.from(u in "users", join: c in "comments", as: :comment, on: c.user_id == u.id)

if Query.has_named_binding?(query, :comment) do
  Query.where(query, [comment: c], c.likes > 0)
else
  query
end

Here is documentation:

has_named_binding?(queryable, key)

Returns true if query has binding with a given name, otherwise false.

For more information on named bindings see “Named bindings” in this module doc.

Source: Ecto.Query.has_named_binding?/2

[rlopzc]

But if it doesn’t have a named binding I still want to do the where clause, so is just repetition on the where query and the only thing that changes is the binding parameters

Maybe I didn’t explained well in the response. Sorry

[rlopzc]

I’m doing a lot of repetition and I wanted a way to refactor it.

For each where clause I’m doing:

if Query.has_named_binding(query, :module) do
  Query.where(query, [module: m], m.something == ^something)
else
  Query.where(query, [m], m.something == ^something)
end

So, I wanted to add that to your custom filter functions but I don’t know how

[Eiji]

My original example was focused on bindings for simplicity. Look that after I answer on your question somebody else may ask:

Can you add also support for [m, n]?

Which just does not makes sense and unnecessarily complicates implementation. Also it makes code less readable i.e. can you tell (without any context) what m or n is? For m it’s simple as we just need to look at from part, but what’s with others? What is x in [m, n, o, p, q, r, s, t, u, w, y, x]?

I would rather do:

Mix.install([:ecto])

alias Ecto.Query
require Query

query = Query.from("posts", as: :post)
query2 = Query.from(p in "posts", as: :post, join: c in "comments", as: :comment, on: c.post_id == p.id)

for current_query <- [query, query2] do
  if Query.has_named_binding?(current_query, :comment) do
    Query.where(current_query, [comment: c], c.likes > 0)
  else
    Query.where(current_query, [post: p], p.likes > 0)
  end
end