Conditionals in Ecto Queries

Hey hey.

I had a fairly productive refactoring session on a small function that I thought I would write about. In Galley, my recipe/cooking application, I have a home / index page where user's can search and view recipes. At the current state, a user can search by recipe name and can filter by all recipes or their recipes. At the moment I am building out a tagging functionality such that a user can search by tags shared between recipes such as spicy, salad, or easy, for example.

With these three sources of permutation, I need to account for a database query that could have a mixed bag of search queries, tags, and filters.

The original code

My original function was pretty bad, but it worked while I was prototyping. It looked like this:

def search_recipes(%{"filter" => filter, "query" => query, "tags" => _tags}, user_id) do
    cond do
      filter == "All" and query == "" ->
        list_recipes()

      filter == "All" and query != "" ->
        from(r in Recipe, where: ilike(r.title, ^"%#{query}%"))
        |> Repo.all()
        |> Repo.preload(:user)

      filter == "My Recipes" and query == "" ->
        from(r in Recipe, where: r.user_id == ^user_id)
        |> Repo.all()
        |> Repo.preload(:user)

      filter == "My Recipes" and query != "" ->
        Recipe
        |> where([r], r.user_id == ^user_id)
        |> where([r], ilike(r.title, ^"%#{query}%"))
        |> Repo.all()
        |> Repo.preload(:user)

      filter == "My Recipes" and query != "" ->
        from(r in Recipe, where: ilike(r.title, ^"%#{query}%"))
        |> Repo.all()
        |> Repo.preload(:user)

      true ->
        list_recipes()
    end
  end

If you find the code above confusing, the pseudocode version is that I walk through the possible states of the two (out of three) fields and make corresponding database queries for each case. This worked well while I was building the search and filter features, but with tags being added I was about to have a whole slew of new permutations - a user might search by tags for recipes they have submitted, but not have any search query. Something had to change!

(To be fair to myself, the above was one of those functions I write when I'm working quickly to get something else working - but I'm glad I came back to refactor it.)

I got started by googling things like "Ecto conditional query" or "Ecto multiple where clauses". That led me here where I learned that I was not the only one running into this sort of situation - and I learned how I could make this a lot simpler:

A refactoring

@doc """
  Search recipes is a bit complex because there are multiple cases we need
  to handle for when we dispatch the DB query.
  """
  def search_recipes(%{"filter" => filter, "query" => query, "tags" => _tags}, user_id) do
    from(r in Recipe)
    |> maybe_filter_recipes(filter, user_id)
    |> maybe_where_by_search(query)
    |> Repo.all
  end

  defp maybe_where_by_search(ecto_query, search_query) do
    if search_query !== "" do
      where(ecto_query, [schema], ilike(schema.title, ^"%#{search_query}%"))
    else
      ecto_query
    end
  end

  defp maybe_filter_recipes(ecto_query, filter_query, user_id) do
    cond do
	    filter_query === "All" ->
        ecto_query

      filter_query === "My Recipes" ->
        where(ecto_query, [schema], schema.user_id == ^user_id)

      true ->
        ecto_query
    end
  end

I could also inline the two maybe_* functions, but I'm fine with them being private functions for now (I prefer it as I like to see my |> pipelines be pretty free of clutter).

Going over the above sample - Ecto is now passing the query from one function to another. This allows me to chain conditional changes to the queries, or just leave them alone and return them to be passed along to the next function.

Pretty neat!

How would I add Tagging to this?

Unfortunately, in order to work with tags, I will have to do some refactoring about this - as Tags involve searching a different table until I get the Recipe ID's I need, which can then have the filter or search query applied to them.

I haven't figured out how to do this yet, but here's a sneak peek in case anybody has ideas (say hi!).

def get_by_tags(tag_list) do
    q =
      from tags in Tag,
        join: recipe_tags in Galley.Recipes.RecipeTag,
        on: recipe_tags.tag_id == tags.id,
        # tag must be in the tag_list
        where: tags.name in ^tag_list,
        # get just recipe ids
        select: recipe_tags.recipe_id,
        # remove duplicate ids
        group_by: recipe_tags.recipe_id

    Repo.all(q)
  end

  get_by_tags(["spicy", "vegetarian"])  # => [10, 24, 53]

The above returns a list of Recipe ID's. So what will I do next? I'm not sure. I could just query for those Recipes by their ID's and then manually filter them in Elixir (this doesn't feel right - there's probably a database-y-ecto-way to do it!). I'd like for the tag searching to fit into the pipeline I refactored to that this post was originally about. I'm not sure yet, but I can tell you one thing - when I do get it working it will be sloppy and hacky, and I'll love it for what it is.

Until next time, thanks for reading!