sirfitz

sirfitz

How do I use a dynamic comparison operator in Ecto?

Hi Everyone! :slight_smile:

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!

Marked As Solved

Eiji

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):

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! :heart:

Also Liked

fuelen

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

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

Eiji

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

Where Next?

Popular in Questions Top

sergio
In Ruby, I can go: User.find_by(email: "foobar@email.com").update(email: "hello@email.com") How can I do something similar in Elixir? ...
New
_russellb
I want to try my hand at web scraping. What tools/libraries do I need to use. I’m hoping to turn this into something professional so don’...
New
Werner
Hi, I’m using Ubuntu 18.04 and after updating to OTP-24.0 yesterday i have this warning when I run “mix local.hex”: 14:57:30.512 [warn] ...
New
ashish173
I am using Ecto timestamps with postgres, I can see the timestamps() use the :naive_dateime but for my use case I wanted to store the ti...
New
qwerescape
Is there a way to get the call stack or stack trace at any point in the code? Not from exceptions, but an expression that returns how the...
New
fireproofsocks
Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? https://hexdocs.pm/ecto/Ecto.Repo.h...
New
ovidiubadita
Hey all, I discovered Elixir and I love it. I always wanted to learn a functional programming and I intended to go for Haskell, but afte...
New
beno
I will often find my self writing things similar to: case some_value do nil -&gt; something() "" -&gt; something() _ -&gt; someth...
New
Codball
Mix format works fine if run from the cmd. I’ve followed this to facilitate the implementation into VSC which involves downloading an ext...
New
Mooodi
Given a string, how can I get access to its character by index? Enum.at("my_string", 2) doesn't work. Or rather, not char, but a substr...
New

Other popular topics Top

JakeBecker
TL;DR: I’ve just released an implementation of Microsoft’s IDE-independent Language Server Protocol for Elixir. It adds language support ...
1140 51847 244
New
sergio
In Ruby, I can go: User.find_by(email: "foobar@email.com").update(email: "hello@email.com") How can I do something similar in Elixir? ...
New
bsollish-terakeet
Credo is smart enough to check for (something like) this: assert length(the_list) == 0 with this response: Checking if an enum is empt...
New
yawaramin
In the Dialyzer docs ( http://erlang.org/doc/man/dialyzer.html#requesting-or-suppressing-warnings-in-source-files ), there is a way to tu...
New
albydarned
Hello all! I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
New
Jim
As a follow up to my earlier question: I have the code compiling and running but not getting a successful login from the rest server. ...
New
polypush135
As many of you may have realized by now (sorry for all the posts here) I’ve been working on a db problem where I’m trying to aggregate a ...
New
aadeshere1
I have a another noob question about loop. Since elixir is immutable, while loop is not directly possible. total = 10 while total != 0 ...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
Patoshizzle
After calling mix ecto.create I get this error: 17:00:32.162 [error] GenServer #PID&lt;0.412.0&gt; terminating ** (Postgrex.Error) FATAL...
New

We're in Beta

About us Mission Statement