Overview

Yesterday, while working on a RESTful API, I encountered a somehow tricky problem.

How do I filter an Ecto result based on the parameters passed in the URL?

In this mini-tutorial, we’ll build a reusable module that will handle the filtering.

Situation

We built an API endpoint for getting all the user’s todo items. The todos table has a state column and each todo item can have a state of done, doing or pending.

What we are trying to achieve

We want to filter the query results based on the request URL’s parameters. For example, we should only include todo items that have the doing or `done` state when the user calls this endpoint:

https://my-awesome-api.io/v1/todos?filter[state]=doing,done

Boilerplate

Usually, to get all the user’s todo items, we have this in our context file:

Todos Context Boilerplate

And we call this function in our controller like this:

Todos Controller Boilerplate

Solution

Since the filter parameters will be passed in the URL, we need to pass the params variable to our context’s list_todos/2 function. We need to modify our controller as such:

By doing that, we also need to adjust the arity of our list_todos function in our context. We’re now also going to prepare our context in using the filter module that we will be building later.

NOTE: FilterEx is the name of the module that we will building later.

In line 7, we just checked if the filter parameter exists or not. If it exists, we return the filter parameters in a list form and if it does not, we just return an empty list.

We will now build the FilterEx module which will handle the filtering.

Building the FilterEx Module

In building this module, we will make of use of Ecto.Query.dynamic/2. If you want to learn more about it, here’s the official documentation. Basically, Ecto.Query.dynamic/2 let’s us build query expressions bit by bit and interpolate it later in our main query.

“… interpolate it later in our main query”

You may be wondering why we did not just add the call to FilterEx in our context like so: where([t], t.user_id == ^user.id and ^FilterEx.filter(states, :state))

There’s a catch though.

dynamic can be interpolated at the root of a where, having or a join’s on.

That’s why we added our FilterEx call after our firstwhere clause and not just interpolate it. It must be interpolated at the root of the clause.

Let’s now define the module and import the necessary Ecto module.

defmodule FilterEx doimport Ecto.Query

...

end

FilterEx.filter/3 expects the first parameter to be the working query, second parameter to be the list of filters and the third parameter to be the column name.

defmodule FilterEx doimport Ecto.Query

@spec filter(Ecto.Query.t, list, atom) :: Ecto.Query.tdef filter(query, [head | tail], fieldname) do...end

def filter(query, [], _), do: query

end

Inside the filter/3 function, we will be building our initial dynamic query which will be passed to the filter_field/3 function for further dynamic query building. Then we interpolate the dynamic_query to our main query and return it to the pipeline.

...

def filter(query, [head | tail], fieldname) dodynamic_query =dynamic([q], field(q, ^fieldname) == ^head)|> filter_field(tail, field_name)

query |> where(^dynamic\_query)   

end

...

We’ll be using recursion method in our filter_field/3 function.

...

def filter_field(dynamic, [head | tail], fieldname) dodynamic([q], field(q, ^fieldname) == ^head or ^dynamic)|> filter_field(tail, fieldname)end

...

We have now finished building the FilterEx module.

Here’s the completed version:

Thank you for reading. If you have any questions, you can always talk to me on Twitter @VinceUrag

Learned something valuable? You can always buy me a coffee. ❤

Connect with me on Github:

vinceurag (Vince Urag)_vinceurag has 11 repositories available. Follow their code on GitHub._github.com