QueryBuilder - Compose Ecto queries without effort

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:

11 Likes

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

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

Thanks for the replay.

Also if you have time:

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

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)

2 Likes

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

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

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

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