sirfitz

How do I use a dynamic comparison operator in Ecto?

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:

A user wants to find all posts that were created by users who joined before October 2020
A user wants to find all posts that were created by users who joined after October 2020
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!

9 comments

#ecto #postgres #postgresql #ecto-query

0 3433 9

2021-03-01 01:30:50 UTC

Marked As Solved

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

Note: Mix.install/1 (new useful feature for writing scripts) is available since Elixir version 1.12.0 (currently in master branch):
https://github.com/elixir-lang/elixir/commit/af787a48fe6b1afd298f2de05d2eb2dbc3b13ead

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:
https://github.com/MikaAK/ecto_shorts/blob/master/lib/query_builder/schema.ex

Have fun!

Post #2

Also Liked

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>

Post #1

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

Post #6

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

Post #9