QueryBuilder - Compose Ecto queries without effort

mathieuprog

I’ll start right with an example

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:

https://github.com/mathieuprog/query_builder

21 comments

/phoenix #ecto #library #contexts #query_builder

13 5047 21

2021-09-22 14:00:27 UTC

Most Liked

Show Best Posts
Show All (oldest first)
Show All (newest first)

mathieuprog

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)

Post #4

mathieuprog

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)

Post #6

wolfiton

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

Post #1

Last Post!

mathieuprog

QueryBuilder v1.0.0 has been released!

New features:

add the Extension module allowing to easily extend QueryBuilder with user’s own functions
add support for offset and limit operations
differentiate field names vs an atom value with the syntax :<field_name>@self vs :<atom_value>

Thanks to @onomated that did all of the work, of high quality!

Because QueryBuilder is considered stable and there have been breaking changes, it has been bumped to 1.0.

Breaking changes:

.1.
QueryBuilder.order_by(User, lastname: :asc) becomes
QueryBuilder.order_by(User, asc: :lastname)

.2.
QueryBuilder.where(User, {:name, :eq, :nickname}) becomes
QueryBuilder.where(User, {:name, :eq, :nickname@self})
(concerns a comparison of two fields of the root schema)

Post #22