Phoenix Query Params to Ecto Queries

Faceted navigation and search filtering can be a tricky problem to work on. There are whole companies and frameworks dedicated to solving this problem; most popularly, Algolia and ElasticSearch.

Recently, I built a dashboard app, Outbox Metrics, that would allow me to see and search my outbox metrics for Amazon SES, their transaction email service. In other projects I've used Algolia for search and filtering, but I wanted to see what it would look like to construct in Elixir and Phoenix.

This is what I wanted the controls to looks like:

Whenever a value of the form changes, we automatically fire off a GET request to /emails and set the appropriate query parameters under a top level "search" param.

And here's a list of all the things we are interested in searching by:

  • q - Generic search box that queries both subject lines and recipient's email addresses
  • date - Users are able to limit searches based on the last 7, 30, 60, or 90 days. Anything longer is too resource intensive. People send a lot of emails...
  • from - Filter on the sending email (help@, support@, billing@, etc.)
  • status - We have one db column representing status, with only 3 possible states. I decided each status would have a query param that is either true or false
    • delivered (did they get it?)
    • bounced (hard/soft, inbox full, ootm, etc.)
    • complaints (spam, unsubscribed)

Having the statuses structured this way made it easy to query the db and minimize the amount of JavaScript I had to write.

Before I went about the business of writing any Elixir code, I started off by writing down my strategy:

  1. Create the UI and html forms
  2. Explain example searches in plain words. "emails in the past 7 days that have bounced"
  3. Write the raw SQL query
  4. Write the corresponding Ecto query
  5. Hook up the controller code to the query code

Our search params look like this in the email controller below:

  %{
    "bounced" => "false",
    "complaint" => "false",
    "sent" => "false",
    "date" => "30",
    "from" => ""
    "q"    => ""
  }

def index(conn, %{"search" => search_params} = params) do
  search_params = search_params |> convert_to_keywords

  {emails, kerosene} = Email
                      |> QueryBuilder.filter_by_search(search_params)
                      |> QueryBuilder.filter_by_from(search_params)
                      |> QueryBuilder.filter_by_status(search_params)
                      |> QueryBuilder.filter_by_date(search_params)
                      |> order_by(desc: :date_sent)
                      |> Repo.paginate

  render conn, "index.html",
    emails: emails,
    stats: Stats.fetch(search_params),
    kerosene: kerosene,
    search_params: search_params
end

In the same way that Plug takes a conn (and some opts) and returns a new, modified conn; QueryBuilder takes an Ecto query (and search params) and returns a new, modified Ecto query.1

Our QueryBuilder looks like this:

defmodule OutboxMetrics.QueryBuilder do
  import Ecto.Query

  def filter_by_from(query, params) do
    params[:from]
    |> case do
      nil  -> query
      ""   -> query
      text -> query |> where(from: ^text)
    end
  end

  def filter_by_date(query, []), do: query
  def filter_by_date(query, list) do
    interval = list[:date] |> String.to_integer

    date = Timex.now
           |> Timex.shift(days: -interval)
           |> Ecto.DateTime.cast!

    query
    |> where([e], e.date_sent > ^date)
  end

  def filter_by_status(query, params) do
    filters = set_status(params)

    query
    |> where([e], e.status in ^filters)
  end

  defp set_status(params) do
    params
    |> Keyword.take(~w(bounced sent complaint)a)
    |> Keyword.values
    |> Enum.uniq
    |> Enum.count
    |> case do
      1 -> ["sent", "bounced", "complaint"]
      _ -> fetch_status_filters(params)
    end
  end

  defp fetch_status_filters(params) do
    params
    |> Keyword.to_list
    |> Enum.filter(fn({k, v}) ->
      v == "true"
    end)
    |> Enum.map(fn({k,v}) ->
      Atom.to_string(k)
    end)
  end

  def filter_by_search(query, params) do
    params[:q]
    |> case do
      nil  -> query
      ""   -> query
      text -> query |> where([r], ilike(r.subject, ^("%#{text}%")))
    end
  end
end
  

Pipes vs. Pattern Matching

I love the pipeline operator. In some of these filter_by functions, I pass the result of a prior pipe into the `case` macro and deal with that way. IMHO, it makes Elixir extremely readable and the code is shaped nicely.

If I wanted to rewrite without using the case statement, I could easily pipe into a new function, pattern match the result and returns what's needed. Rewritten, filter_by_search looks like:

def filter_by_search(query, params),
  do: params[:q] |> search_query

def search_query(nil), do: query
def search_query(""),  do: query
def search_query(text),
  do: query |> where([r], ilike(r.subject, ^("%#{text}%")))

It's actually the same number of lines, but for some reason I prefer piping into case. Although, now I'm curious if there are performance benefits in using pattern matching when Elixir compiles down to Erlang bytecode.

Let's breakdown the filter_by_status function:

  def filter_by_status(query, params) do
    filters = set_status(params)

    query
    |> where([e], e.status in ^filters)
  end

  defp set_status(params) do
    params
    |> Keyword.take(~w(bounced sent complaint)a)
    |> Keyword.values
    |> Enum.uniq
    |> Enum.count
    |> case do
      1 -> ["sent", "bounced", "complaint"]
      _ -> fetch_status_filters(params)
    end
  end

The set_status function is basically looking to see if all the status boxes are checked or none are. That determines how we structure the where clause in our filter. If 1 or 2 boxes are checked, we pass params into fetch_status_filters and return the list of strings that were checked as "true" from our view.

Summary

This is basically a really simple use-case of how you can translate search filters from a UI to Ecto queries. The other 2 posts I've heavily on are Drew Olson's Composable Queries with Ecto and Lew Parker's Ecto Query Examples.

One of my biggest takeaways from Drew's post was separate out your Ecto query functions into a module. By doing this you get a nice, clean pipeline in your controller:

def index(conn, %{"search" => search_params} = params) do
  {emails, kerosene} = Email
                      |> QueryBuilder.filter_by_search(search_params)
                      |> QueryBuilder.filter_by_from(search_params)
                      |> QueryBuilder.filter_by_status(search_params)
                      |> QueryBuilder.filter_by_date(search_params)
                      |> order_by(desc: :date_sent)
                      |> Repo.paginate

  ...
end

Footnotes

1 . In case you're wondering, Kerosene is a pagination plugin and Repo.paginate returns a tuple of a list of N records and some data about pagination.