QueryBuilder - Compose Ecto queries without effort

Your Libraries & OS Mentoring Libraries
, , ,
1

I’ll start right with an example :point_down:

User
|> QueryBuilder.where(firstname: "John", city: "Anytown")
|> QueryBuilder.where({:age, :gt, 30})
|> QueryBuilder.order_by(lastname: :asc)
|> QueryBuilder.preload([:role, authored_articles: :comments])
|> Repo.all()

With associations:

User
|> QueryBuilder.where([role: :permissions], name@permissions: "delete")
|> Repo.all()

Query Builder allows to build and compose Ecto queries based on data.
Concise, no need to deal with bindings and macros.

Its primary goal is to allow Context functions to receive a set of filters and options:

# in a Controller
Blog.list_articles(preload: [:comments], order_by: [title: :asc])
Blog.list_articles(preload: [:category, comments: :user])

This avoids having to create many different functions in the Context for every combination of filters and options, or to create one general function that does too much to satisfy all the consumers.

The calling code (e.g. the Controllers), can now retrieve the list of articles with different options. In some part of the application, the category is needed; in other parts it is not; sometimes the articles must be sorted based on their title; other times it doesn’t matter, etc.

The options may be added to the query as shown below:

# in the Blog context
def get_article_by_id(id, opts \\ []) do
  QueryBuilder.where(Article, id: id)
  |> QueryBuilder.from_list(opts)
  |> Repo.one!()
end

Inspired by the libraries token_operator and ecto_filter.

More examples are available in the doc:

13 Likes
2

Hi,

Did you checked your library with a security package for elixir for sql injections or other problems?

Also is it compatible with absinthe and absinthe_ecto?

Thanks in advance

1 Like
3

The library makes use of Ecto.Query to build queries, so it comes with the same security features as Ecto. SQL injection is impossible; Ecto always uses parameterized queries which prevent SQL injection attacks.

I still have to gain more knowledge about Absinthe, which I plan to delve into in the coming months. So I can’t comment about that now sorry:) But I will look into that, thank you for the idea.

1 Like
4

Thanks for the replay.

Also if you have time:

Does your library make the queries less costly or it just replaces Ecto queries?

5

It only does a small optimization for you when you want to preload data.

Imagine a User has one Role and many Articles.

If you want to preload the user with its associated role and articles, it’s better to join user and role table (as it is a one-to-one association); but it is better to execute a separate query for loading the articles (the cost of executing a separate SQL query to the DB for loading a one-to-many association is lower than Ecto’s processing if all the rows are in the result of one single query).

If you execute the following:

QueryBuilder.preload(User, [:role, :articles])
|> Repo.all()

It will generate something like:

from u0 in User,
 join: r1 in assoc(u0, :role),
 preload: [:articles],
 preload: [role: r1]

As you can see above, the library has joined user and role as it is a one-to-one association, but didn’t join articles with user (as long as there are no where clauses on articles, in which case there will be of course a join).

That’s the only optimization regarding queries it does for you. But you could write those queries directly with Ecto.Query’s API of course. The purpose of the library is really to work with data instead of macros, which allows you to

  • have a more flexible API for your Context; Context functions can receive data (querying options such as what you want to preload, if you want to sort, pagination params, etc.) which can be passed to QueryBuilder;
  • compose queries more easily without having to take into account binding positions or arbitrary named bindings.

There’s still a lot of work to be done, and the library will be updated progressively according to my needs or other library users’ need. Currently supported are basic where clauses:
QueryBuilder.where(User, age: 30) ( == )
same as: QueryBuilder.where(User, {:age , :eq, 30})
QueryBuilder.where(User, {:age, :ne, 30}) ( != )
QueryBuilder.where(User, {:age, :gt, 30}) ( > )
QueryBuilder.where(User, {:age, :ge, 30}) ( >= )
QueryBuilder.where(User, {:age, :lt, 30}) ( < )
QueryBuilder.where(User, {:age, :le, 30}) ( <= )

You can pass a list with multiple filters:
QueryBuilder.where(User, name: "Bob", age: 30)

With associations:
QueryBuilder.where(User, :role, name@role: "author")
QueryBuilder.where(User, [role: :permissions], name@permissions: "write")

Order by:
QueryBuilder.order_by(User, age: :desc)
QueryBuilder.order_by(User, :articles, title@articles: :asc)

The functions above will make the necessary joins automatically, but sometimes you need to left join:
QueryBuilder.join(User, :articles, :left)

And of course, preload:
QueryBuilder.preload(User, :articles, role: :permissions)

3 Likes
6

Thank you for providing examples and explanations of your libraries features.
The way you design it it looks very refreshing form the traditional Ecto queries and it looks a lot more human friendly and short.

Also i think will help other team members to understand easily the code base.

I will give it a try and come back with the results in a couple of days.

Thanks for sharing it.

1 Like
7

Here are some new convenient features in QueryBuilder:

Grouped OR expressions:

query
|> QueryBuilder.where([], [name: "John"], or: [name: "Alice", age: 42], or: [name: null])
|> QueryBuilder.where(:address, [address@city: "Venice"], or: [address@city: null])

maybe_where/3 for easier piping:

query
|> QueryBuilder.where(name: "Alice")
|> QueryBuilder.maybe_where(some_condition, age: 42, active: true)
3 Likes
8

Hey, thanks for sharing your work!
How about supporting aggregate functions, like count? i.e. How to do it with QueryBuilder without making extra DB calls?

Also, do you plan to add support for pagination-related queries?

I really like how Flop library simplifies some things returning the meta info, but unfortunately it lacks some features that QueryBuilder or ExSieve have, eg. for contains operations.

1 Like
9

I add features according to my needs or others’ demands. For example, someone asked support for IN, so I added :in, :not_in, :include, :exclude operations.
Latest update with support for grouped OR expressions and maybe_where were added for my own project’s need.

Also, do you plan to add support for pagination-related queries?

I have personally no experience yet with pagination in Ecto, so I will have to wait that I encounter the need of such operations in my project.
Thank you for bringing that up:)

1 Like
10

First off, thanks for an awesome library.
Any guidance on how to support fragments in where queries? So I’m implementing text search, and needs some logic similar to this:

 from(u in query,
    where: fragment("? % ?", u.username, ^search_term),
    order_by: fragment("similarity(?, ?) DESC", u.username, ^search_term))

Notice the fragment needs to access columns bound to the queryable object.
I’m extending QueryBuilder with my own app module to add the search function, which should just work with from_list. Something like:

defmodule MyApp.QueryBuilder do
  @moduledoc """
  Convenience wrapper around `QueryBuilder` (https://github.com/mathieuprog/query_builder) module
  that provides a few extra utilities
  """

  import QueryBuilder

  defmacro __using__(opts) do
    quote do
      require QueryBuilder
      QueryBuilder.__using__(unquote(opts))

      alias MyApp.QueryBuilder, as: QB
    end
  end

  def search(query, search_term) do
    # How to extend the query builder to accept search operations? Can't bind query variables
    where(query, fragment("? % ?", u...))
    |> (query, fragment("similarity(?, ?), :desc)
  end
end

Is there a way to support custom sql fragments? Or looks like one approach would be to break down the QueryBuilder.Query down to ecto_query? Any guidance on extending would be appreciated!

1 Like
11

Here is an example of how to extend the query with Ecto when using QueryBuilder:

text_equals_condition =
  fn (field, value, get_binding_fun) ->
    {field, binding} = get_binding_fun.(field)
    Ecto.Query.dynamic([{^binding, x}], fragment("initcap(?)", ^value) == field(x, ^field))
  end

User
|> QueryBuilder.where(&text_equals_condition.(:name, "alice", &1))
|> Repo.all()

How does it work?

  • We pass a function to where/2. That function will be called by QueryBuilder and the library will pass a function that allows you to get the right binding for a field.
User
|> QueryBuilder.where(User, 
  fn get_binding_fun -> text_equals_condition.(:name, "alice", get_binding_fun) end)
  • You build a dynamic expression and you add fields with their bindings (by calling the function that the library provides you with):
text_equals_condition =
  fn (field, value, get_binding_fun) ->
    {field, binding} = get_binding_fun.(field)
    Ecto.Query.dynamic([{^binding, x}], fragment("initcap(?)", ^value) == field(x, ^field))
  end

I noticed that this works for where but not for order_by yet, because dynamic hasn’t been used for order_by. However I guess it will be easy to add.

By the way, it would be nice to add your module in the doc for demonstrating how to extend QueryBuilder. Would you be able to past a working code of the module once you’re done (and a sample calling code)?

12

I made order_by also using dynamic queries and added support for extending order_by with a custom Ecto dynamic query. Example:

character_length =
  fn (field, get_binding_fun) ->
    {field, binding} = get_binding_fun.(field)
    Ecto.Query.dynamic([{^binding, x}], fragment("character_length(?)", field(x, ^field)))
  end

User
|> QueryBuilder.order_by(asc: &character_length.(:name, &1))
|> Repo.all()

You’ll have to depend on master branch for the moment though, because I had to inverse the direction order and field:

QueryBuilder.order_by(User, lastname: :asc)

becomes

QueryBuilder.order_by(User, asc: :lastname)

:man_facepalming:

13

Thanks @mathieuprog! That’s great. I’ll try out the master branch and share my implementation with you.

1 Like
14

@mathieuprog, here’s a gist where I started expanding the QueryBuilder functionality:

It’s currently work in progress as I haven’t added the necessary logic to utilize your changes to order_by in your master branch. Maybe making QueryBuilder a behavior with default implementations will make extending a lot more straightforward i.e. remove the need to defdelegate functions.

An option to avoid breaking your order_by interface would be to support a map of the order by spec, and just check if its a keyword list or map. So supporting something along the lines of:

QueryBuilder.order_by(User, %{direction: :asc, field: :last_name})

Just a thought.

I’ll expand my search functionality to also utilize your current changes for order_by as well and update the gist afterwards

15

@mathieuprog Tested out the custom sql functionality for order_bys and it works great. I’ve updated the gist above with my QueryBuilder extension module that utilizes dynamic bindings in where and order_by methods. Looking forward to the next release with these features.

Thanks once again for supporting the ask. Cheers!

16

It’s a good idea to offer a module to allow extension! I don’t see how a behaviour would help here as you want to extend the QueryBuilder module with your own custom functions with arbitrary function names.
What we could offer though is some code injection: use QueryBuilder.Extension and this will allow to inject the from_list as well to accept the new custom functions.
If you want to change your codebase already you could make a PR, or you can just wait until I’ll get back into it:)

The lib is in alpha so I’m allowed to break it? :smiley: But what I will do I think is make a stable version 1.x and it won’t bother anyone. Having e.g. asc: :lastname also follows the Ecto Query API option.

17

Interesting. Can you highlight a bit more about what you’re thinking here? This is a pattern I haven’t seen. Not as much of a power user of Elixir just yet, so it’ll be an interesting learning experience.

Great point. Yes this does align with the Ecto Query API arg scheme.

One issue I have run into is that QueryBuilder assumes atom values are fields. So queries like this:

args_collected_somewhere = [auth_method: :email, auth_val: "someone@example.com"]
User
|> QueryBuilder.where(args_collected_somewhere)
|> Repo.one!()

is resolving to the following sql and error:

SELECT a0.* FROM "users" AS a0 WHERE ((a0."auth_val" = $1) AND (a0."auth_method" = a0."email"))

** (Postgrex.Error) ERROR 42703 (undefined_column) column a0.email does not exist

This is problematic as I use enums (with GitHub - gjaldon/ecto_enum: Ecto extension to support enums in models) somewhat extensively in my data model, so attempting to “know enum fields” and programmatically convert them to strings first will be tedious. Is there a way to be explicit about values actually being db columns and only then should they be auto-binded as fields? I can file an issue if there is no workaround for this at the moment. This was caught in my regression tests as I’m trying to incorporate QueryBuilder.to_list where this worked fine prior:

args_collected_somewhere = [auth_method: :email, auth_val: "someone@example.com"]
User
|> Repo.get_by!(args_collected_somewhere)
18

The idea is to simply inject all those defdelegates and the from_list function into the custom module. Except if I missed something? use allows you to inject code.

Nice, obviously I didn’t think about that case:) I think it’s easily solved with some reflection:
https://hexdocs.pm/ecto/Ecto.Schema.html#module-reflection

As a note to self, I think I’d also like at some point to implement select, because QueryBuilder is limited to fetching full entities right now.

19

Ah, so the thought is allow clients create an extension module that are passed in to QueryBuilder like so?

use QueryBuilder, extension: MyApp.QueryBuilder.SearchFunction

MyApp.QueryBuilder

defmacro __using__(opts) do
    extension = Keyword.get(opts, :extension, QueryBuilder.NullExtension)
    # confirm its a module with __using__
    quote do
      require QueryBuilder.Schema
      QueryBuilder.Schema.__using__(unquote(opts))
    
      require unquote(extension)
      unquote(extension).__using__(unquote(opts))
    end
  end

The clients can use just query builder as currently documented. No defdelegates required and no from_list overrides needed. Its worth a shot, though injecting macros can be complex if the extension macros have other macro operations such as setting module attributes which my extension module does. It’s worth a try to see how that plays out in my case.

That’s one approach, or introducing :self as a sentinel for the current schema to resolve to a field. So:

User
|> QueryBuilder.where([auth_method: :email@self, auth_val: "someone@example.com"])
|> Repo.one!()

Resolves to the current functionality as today, and:

User
|> QueryBuilder.where([auth_method: :email, auth_val: "someone@example.com"])
|> Repo.one!()

works like Ecto does today, while modeling the current association syntax you have.

Thoughts? Either way, it’s worth creating issues, and I can take a stab at working on the functionality suggested in a new branch or just fork the repo and PR back in after implementing. Definitely need to address the atom field referencing to use this lib

20

I thought rather something like:

defmodule MyApp.QueryBuilder do
  use QueryBuilder.Extension # inject defdelegate and from_list

  def search(query, field, search_term) do
    # code
  end
end

and in QueryBuilder.Extension have __using__ injecting the defdelegates and from_list.

Except if I’m missing something :smiley:

Note that it really is for the from_list function, otherwise I would have suggested to not extend QueryBuilder at all with macros, and just do something like:

User
|> QueryBuilder.where(firstname: "John")
|> QueryBuilderExt.search(...)

However the user might want to use from_list with the new functionality.

I prefer reflection because I think it’s not expensive at all, as I think the values from these functions for reflection are calculated at compile time.

1 Like