Introducing QueryElf

Posted on November 02, 2020

So, you’ve started working on a brand new project: a blog engine (how original, I know, but bear with me).

As you are working with the admin interface that lists blog posts, you get the following requirement:

It should be able to list all blog posts

“That should be simple enough”, you think, which leads you to the following implementation:

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.Post

  def list_posts do
    Repo.all(Post)
  end
end

Simple enough right?

But then, as the business needs evolve, so does the feature’s requirements, which now reads like this:

It should be able to list all blog posts, allowing the user to filter by the post’s author.

Thanks to Ecto’s great query composing features, extending the previous implementation to accommodate the new requirement should be an if statement away:

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.Post

  import Ecto.Query

  def list_posts(filter) do
    query = Post

    query =
      if filter[:author_id] do
        where(query, author_id: filter[:author_id])
      else
        query
      end

    Repo.all(query)
  end
end

Hmmm, not as clean, but it should be good enough for now. However, life is not that simple, and yet a new change is required to the feature:

It should be able to list all blog posts, allowing the user to filter by the post’s author and the post’s released state.

Lets try to extend the previous approach to accommodate this:

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.Post

  import Ecto.Query

  def list_posts(filter) do
    query = Post

    query =
      if filter[:author_id] do
        where(query, author_id: filter[:author_id])
      else
        query
      end

    query =
      if filter[:is_released] do
        where(query, is_released: filter[:is_released])
      else
        query
      end

    Repo.all(query)
  end
end

Okay, this is starting to look bad, especially as more and more filters are added to this function. Thankfully, we are using Elixir and can leverage pattern matching to make this code look cleaner:

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.Post

  import Ecto.Query

  def list_posts(filter) do
    Post
    |> filter_posts_by_author_id(filter[:author_id])
    |> filter_posts_by_is_released(filter[:is_released])
    |> Repo.all()
  end

  defp filter_posts_by_author_id(query, nil), do: query
  defp filter_posts_by_author_id(query, author_id) do
    where(query, author_id: author_id)
  end

  defp filter_posts_by_is_released(query, nil), do: query
  defp filter_posts_by_is_released(query, is_released) do
    where(query, is_released: is_released)
  end
end

That is much better!

Can we improve on this though? As a matter of fact, yes we can:

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.Post

  import Ecto.Query

  def list_posts(filter) do
    filter
    |> Enum.reduce(Post, &build_post_filter/2)
    |> Repo.all()
  end

  defp build_post_filter(query, {:author_id, author_id}) do
    where(query, author_id: author_id)
  end

  defp build_post_filter(query, {:is_released, is_released) do
    where(query, is_released: is_released)
  end
end

Now adding a new feature is just a matter of adding a new pattern to the build_post_filter function, and no more changes required to the main list_posts function. Awesome!

But just as the code seemed future-proof, this update to the requirements comes in:

It should be able to list all blog posts, allowing the user to filter by the post’s author and the post’s released state, where the filters could be combined using “and” or “or” and multiple instances of each filter could be used.

Mondays, right? - Photo by Bradley Howington on Unsplash

It’s clear at this point that the current design is insufficient to accommodate this new change. What is unclear though, is what changes we could make to solve this. Ecto’s dynamic seems like a good alternative, as it can be used to combine multiple filters using and/or, but even though it is one of our only options at this point, it is a rather complex one.

It also doesn’t solve the issue of having to duplicate this boilerplate code (and its related tests) for every resource that requires anything more complex than the simplest of the filtering options.

To solve this issue (and possibly more), I’d like to introduce you to QueryElf:

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post

  def filter(:author_id, author_id, _query) do
    dynamic([p], p.author_id == ^author_id)  end

  def filter(:is_released, is_released, _query) do
    dynamic([p], p.is_released == ^is_released)
  end
end

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.PostQueryBuilder

  def list_posts(filter) do
    filter
    |> PostQueryBuilder.build_query()
    |> Repo.all()
  end
end

You can see on the highlighted line of the snippet above, that we define our filters using Ecto’s dynamic. This allows us to do some simple and some rather complex filtering:

# posts by author 1
MyApp.Blog.list_posts(author_id: 1)
# released posts
MyApp.Blog.list_posts(is_released: true)
# unreleased posts by author 1
MyApp.Blog.list_posts(author_id: 1, is_released: false)
# posts that are either released or unreleased by author 1
MyApp.Blog.list_posts(_or: [
  is_released: true,
  _and: [author_id: 1, is_released: false]
])

QueryElf also comes with a plugin system that supercharges it’s functionality. One such plugin that comes bundled with it is AutomaticFilters:

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post,
    plugins: [
      {QueryElf.Plugins.AutomaticFilters, fields: [:author_id, :is_released]}
    ]
end

The query builder above provides the exact same functionality as the previous one, and some more:

# posts by authors 1 and 3
MyApp.Blog.list_posts(author_id__in: [1, 3])
# released posts that are not from author 2
MyApp.Blog.list_posts(released: true, author_id__neq: 2)

You can read the plugin’s documentation to check out which filters are defined for each field type.

Armed with that, let’s see how we could handle the next requirement change:

It should be able to list all blog posts, allowing the user to filter by the post’s author and the post’s released state, where the filters could be combined using “and” or “or” and multiple instances of each filter could be used. The user should also be allowed to sort the posts by their creation date.

Thankfully, QueryElf also handles sorting, so it should be easy to solve that by extending our query builder definition:

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post,
    plugins: [
      {QueryElf.Plugins.AutomaticFilters, fields: [:author_id, :is_released]}
    ]

  def sort(:inserted_at, sort_direction, _extra_arg, query) do    sort(query, [p], [{^direction, p.inserted_at}])  endend

defmodule MyApp.Blog do
  alias MyApp.Repo
  alias MyApp.Blog.PostQueryBuilder

  def list_posts(filter, options) do    filter
    |> PostQueryBuilder.build_query(options)    |> Repo.all()
  end
end

And voilà, we have our sorting working:

# you can use a keyword syntax similar to Ecto's
MyApp.Blog.list_posts(filter, order: [asc: :inserted_at])
# you can also use a map-based approach that is more friendly to APIs
MyApp.Blog.list_posts(filter, order: [%{field: :inserted_at, direction: :desc}])

And as you might have guessed, that sorter definition is common enough that QueryElf also bundles in a plugin for that:

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post,
    plugins: [
      {QueryElf.Plugins.AutomaticFilters, fields: [:author_id, :is_released]},
      {QueryElf.Plugins.AutomaticSorters, fields: [:inserted_at]}
    ]
end

With that out of the way, people noticed that the administrative interface does not have any kind of pagination (how you were able to avoid adding it for that long baffles me), so here comes another update to our requirements:

It should be able to list all blog posts, allowing the user to filter by the post’s author and the post’s released state, where the filters could be combined using “and” or “or” and multiple instances of each filter could be used. The user should also be allowed to sort the posts by their creation date and paginate the results with a configurable page size.

Pretty standard stuff, but how should we proceed about it?

Well, one of the great things about using QueryElf is that it is simple in its purpose: building Ecto queries, no more, no less. In essence, this means that you should be able to simply implement your pagination on top of the query built by QueryElf:

defmodule MyApp.Blog do
  import Ecto.Query  alias MyApp.Repo
  alias MyApp.Blog.PostQueryBuilder

  def list_posts(filter, options) do
    {page, options} = Keyword.pop(options, :page, 1)    {per_page, options} = Keyword.pop(options, :per_page, 10)
    filter
    |> PostQueryBuilder.build_query(options)
    |> apply_pagination(page, per_page)    |> Repo.all()
  end

  defp apply_pagination(query, page, per_page) do    offset = (page - 1) * per_page    query    |> limit(^per_page)    |> offset(^offset)  endend

But we should save such techniques for the times where QueryElf does not have our back, which is not the case for offset-based pagination.

We could simply rollback the previous change to our main MyApp.Blog module and make the following change to our query builder:

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post,
    plugins: [
      {QueryElf.Plugins.AutomaticFilters, fields: [:author_id, :is_released]},
      {QueryElf.Plugins.AutomaticSorters, fields: [:inserted_at]},
      QueryElf.Plugins.OffsetPagination    ]
end

And with that set, pagination should be working nicely:

MyApp.Blog.list_posts(filter, page: 1, per_page: 10)

You can also change the default page size used by the plugin (which is 25, BTW):

defmodule MyApp.Blog.PostQueryBuilder do
  use QueryElf,
    schema: MyApp.Blog.Post,
    plugins: [
      {QueryElf.Plugins.AutomaticFilters, fields: [:author_id, :is_released]},
      {QueryElf.Plugins.AutomaticSorters, fields: [:inserted_at]},
      {QueryElf.Plugins.OffsetPagination, default_per_page: 15}    ]
end

There’s also some neat guides on some more advanced usage, be sure to check them out here and here.

And last but not least, if you want to take QueryElf for a spin, and find any issues/have any suggestions, feel free to open an issue in the project’s repository.

That’s all for this post, folks!