Ecto query filter from map

Questions / Help
1

Hi,

I’m in the process of learning phoenix & ecto, and as an exercise I figured I would start porting an existing Django application I wrote for work, to Elixir/Phoenix. The application is a REST API server with no html pages. I’m using the Django Rest Framework also. I’m trying to port features one for one, so that in the event that the Phoenix application is more suitable, we could swap it out without affecting any clients that are using the API.

So far I’ve managed to get the basics working but I’m unsure how best to port the filtering implementation. In the existing application users can filter responses via dictionaries/maps like so:

{"name":"qid","op":"gte","val":10}

or compose them like this

{"and": [{"name": "id", "op": "gt", "val": 2}, {"name": "priority", "op": "lt", "val": 22}]}

These are strings passed as parameters to the endpoint and then processed to create Django queries.
Is it possible to create Ecto queries in a similar way from these filter maps, and if so, what would be the best approach?

2

It is possible, though a bit too long to express in a forum post, especially since you also want boolean logic.

But let’s do a simpler version as an exercise, converting a list of filters into an Ecto query

filters = [
  %{"name" => "qid", "op" => "gte", "val" => 10}, 
  %{"name" => "priority", "op" => "lt", "val" => 22}
]

filtered_items = 
  Item
  |> Enum.reduce(filters, &apply_filter/2)
  |> Repo.all()

def apply_filter(%{"name" => name, "op" => op, "val" => val}, query) do
  # Maybe this can be refactored somehow?
  case op do
    "gte" -> where(query, [q], field(q, ^name) >= ^val)
    "gt" -> where(query, [q], field(q, ^name) > ^val)
    "lt" -> where(query, [q], field(q, ^name) < ^val)
  end
end

This should get you started, perhaps somebody can expand this to add boolean logic

2 Likes
3

Thank you. That’s a perfect place to start, as the key thing I was missing how to convert the strings to the ecto query paramters.

4

Reduce is often the answer when you have a list of something (input filters) and you need to collapse it into a single value (a query).

The complexity then is how to deal with each single element.

1 Like
5

Indeed. Here’s the Python code I wrote for this task https://gist.github.com/Svenito/000eee4a4446eb9a7e8a47c8abfab07c which also uses reduce.

This exercise will no doubt teach me a lot about elixir :smile:

6

For anyone else stumbling on this question, here’s how I managed to get most of the operations implemented in Elixir. Using Ecto’s dynamic queries I’ve managed to pretty much replicate the behaviour from the Python implementation.

https://gist.github.com/Svenito/67436096c5c217706beee9aaa893f787

All that’s really missing is support for not (which I am not sure is really needed as users can just use the equivalent operators) and support for in, [i]startswith, and [i]contains.

The original also support filtering against self. values (eg {"name":"workerid","op":"eq","val":"self.taskid"}), but that shouldn’t prove too much of an issue

Thanks again for the excellent help!

2 Likes
7

Looks pretty good!

A few suggestions:

!(possible_keys |> Enum.any?(&(Map.has_key?(filter, &1)))) reads better if split into multiple lines, and assigned to a variable. And personally I use |> only at the beginning of a line, just adds noise in the middle of an expression IMO.

also

case filter do
  %{"or" => filters} ->
    # You already pattern matched the or condition, you can use `filters` as first argument
    # instead of filter["or"]
    mapped = Enum.map(filters, &parse_filter/1)
    Enum.reduce(mapped, false, fn x, y -> dynamic(^x or ^y) end)
  ...
end

EDIT: unless I’m wrong, you can simplify the whole if and case with this:

def parse_filter(filter) do
    case filter do
      %{"or" => filters} ->
        mapped = Enum.map(filters, &parse_filter/1)
        Enum.reduce(mapped, false, fn (x, y) -> dynamic(^x or ^y) end)

      %{"and" => filters} ->
        mapped = Enum.map(filters, &parse_filter/1)
        Enum.reduce(mapped, false, fn (x, y) -> dynamic(^x and ^y) end)

      %{"name" => name, "op" => op, "val" => val} -> 
        create_query(name, op, val)
    end
  end
1 Like
8

Indeed, I don’t need the first if at all, I can just process it all in the case statement.

I’ve updated the gist

Many thanks

9

I’m getting there, but am now stuck on the support for self.<fieldname>: filter={"name": "value", "op": "eq", "val": "self.id"} which would be: SELECT * FROM items WHERE value = id;

I can make it work with this code snippet

if is_bitstring(val) && String.starts_with?(val, "self.") do
  val = String.replace(val, "self.", "")
  case op do
    "eq" -> dynamic([q], field(q, ^String.to_atom(name)) == field(q, ^String.to_atom(val)))
  end
else
  case op do
    "eq" -> dynamic([q], field(q, ^String.to_atom(name)) == ^val)
  end 
end

But that requires me to have two case blocks, one that handles the self. cases and one that handles regular values. I was hoping to change the value of val if it starts with self. and use that, but it doesn’t seem possible to create a field reference outside of the dynamic query, something like this maybe

field_value = field(q, ^String.to_atom(val))) #what is q though?
dynamic([q], field(q, ^String.to_atom(name)) >= ^field_value)

Is what I am trying to achieve possible? Django uses F Expressions to handle this.

10

Use more of dynamic:

field_value = dynamic([q], field(q, ^String.to_atom(val)))
dynamic([q], field(q, ^String.to_atom(name)) >= ^field_value)
1 Like
11

Thank you, I wasn’t aware that dynamic can be used like that. That’s really useful. Having said that, now I’m stuck with the case of startswith. Originally I had

"startswith" -> dynamic([q], like(field(q, ^String.to_atom(name)), ^"#{val}%"))

but would also like that to support the self. syntax in that case. So in SQL

SELECT * FROM items WHERE control LIKE comment || '%';

I can’t concat a dynamic and a string, so I’m not sure how to achieve this.