QueryBuilder - Compose Ecto queries without effort

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

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.

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)

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.

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)

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.

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:)

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!

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)?