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!

This post was written while watching a supermoon rise over Toronto, with birds chirping in the foreground. I was listening to Erik Hall's rendition of Music for 18 Musicians and a lovely cover of Aphex Twin's Rhubarb.

Edits

2022-05-16

After posting this on the r/elixir subreddit I was given some great feedback about the dynamic macro that could make my code even shorter:

  def search_recipes(%{"filter" => filter, "query" => search_query, "tags" => _tags}, user_id) do
    s_conditions =
      if search_query !== "", do: dynamic([r], ilike(r.title, ^"%#{search_query}%")), else: true

    f_conditions = if filter === "My Recipes", do: dynamic([r], r.user_id == ^user_id), else: true
    and_condition = dynamic([s], ^s_conditions and ^f_conditions)

    from(r in Recipe) |> where([s], ^and_condition) |> Repo.all()
  end

Very nice! If I'm understanding dynamic correctly, I may be able to figure out how to incorporate tags too. I think the answer might lie in subqueries.

2022-05-21

I finally got around to finishing up the feature of adding tags. My conditional querying ended up looking like this:

  @doc """
  Determine the query we are going to make.
  """
  def search_recipes(%{"filter" => filter, "query" => search_query, "tags" => tags}, user_id) do
    s_conditions =
      if search_query !== "",
        do: dynamic([r], ilike(r.title, ^"%#{search_query}%")),
        else: true

    f_conditions =
      if filter === "My Recipes",
        do: dynamic([r], r.user_id == ^user_id),
        else: true

    and_condition = dynamic([s], ^s_conditions and ^f_conditions)

    if String.length(tags) === 0 do
      from(r in Recipe) |> where([s], ^and_condition) |> Repo.all()
    else
      split_tags =
        for tag <- String.split(tags, ","),
            tag = tag |> String.trim() |> String.downcase(),
            tag != "",
            do: tag

      tagged_recipe_ids = get_by_tags(split_tags)
      from(r in Recipe)
        |> where([s], ^and_condition)
        |> where([r], r.id in ^tagged_recipe_ids)
        |> Repo.all()
    end
  end

The last if block was added such that if the user has included tags in their search query, we grab the recipe ids that match it and then use those to get those recipes (/and still use the filters of the search ba and the "my recipes filter").

Not bad! I think I'm doing two queries when I could be doing one (instead of querying for the tags and then getting the id's back, there is probably some SQL magic I could do it all in one query.) but for now I'm happy with this - no need to do preoptimizations.