Query Builder
Query Builder allows you to build and compose Ecto queries based on data.
User
|> QueryBuilder.where(firstname: "John")
|> QueryBuilder.where([{:age, :gt, 30}, city: "Anytown"])
|> QueryBuilder.order_by(asc: :lastname)
|> QueryBuilder.preload([:role, authored_articles: :comments])
|> QueryBuilder.offset(20)
|> QueryBuilder.limit(10)
|> Repo.all()This allows writing queries more concisely, without having to deal with bindings and macros.
Its primary goal is to allow Context functions to receive a set of filters and options:
Blog.list_articles(preload: :comments, order_by: [asc: :title])
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.
See QueryBuilder.from_list/2 below.
Examples
User
|> QueryBuilder.where(firstname: "John")
|> QueryBuilder.where([{:age, :gt, 30}, city: "Anytown"])
|> QueryBuilder.order_by(asc: :lastname)
|> QueryBuilder.preload([:role, authored_articles: :comments])
|> QueryBuilder.offset(20)
|> QueryBuilder.limit(10)
|> Repo.all()Filtering on associations is supported:
User
|> QueryBuilder.where(:role, name@role: "admin")
|> Repo.all()User
|> QueryBuilder.where([role: :permissions], name@permissions: "delete")
|> Repo.all()Article
|> QueryBuilder.where(:author, id@author: author_id)
|> QueryBuilder.where([:author, :comments], {:logged_at@author, :lt, :inserted_at@comments})
|> QueryBuilder.preload(:comments)
|> Repo.all()Usage
Add use QueryBuilder in your schema:
defmodule MyApp.User do
use Ecto.Schema
use QueryBuilder
schema "users" do
# code
end
# code
endYou may also specify the schema's associations to QueryBuilder in order to remedy
some limitations when building queries:
defmodule MyApp.User do
use Ecto.Schema
use QueryBuilder, assoc_fields: [:role, :articles]
schema "users" do
# code
belongs_to :role, MyApp.Role
has_many :articles, MyApp.Article
end
# code
endCurrently, supported operations are:
QueryBuilder.paginate/3
%{paginated_entries: paginated_entries, pagination: pagination} =
User
|> QueryBuilder.order_by(asc: :nickname, desc: :email)
|> QueryBuilder.paginate(Repo, page_size: 10)
%{
cursor_direction: :after,
cursor_for_entries_before: cursor_for_entries_before,
cursor_for_entries_after: cursor_for_entries_after,
has_more_entries: has_more_entries?,
max_page_size: 10
} = pagination
# for subsequent request you may use the cursor:
User
|> QueryBuilder.order_by(asc: :nickname, desc: :email)
|> QueryBuilder.paginate(Repo, page_size: 10, cursor: cursor_for_entries_after, direction: :after)You may configure a global default page size via config:
config :query_builder, :default_page_size, 100QueryBuilder.where/2
QueryBuilder.where(query, firstname: "John")QueryBuilder.where/4
QueryBuilder.where(query, [role: :permissions], name@permissions: :write)Above where functions support different filter operations, for instance:
QueryBuilder.where(query, {:age, :greater_than, 18})Supported filter operations are:
-
:equal_to(or:eq) -
:other_than(or:ne) -
:greater_than(or:gt) -
:greater_than_or_equal_to(or:ge) -
:less_than(or:lt) -
:less_than_or_equal_to(or:le) :like:ilike:starts_with:ends_with:contains
Array inclusion checking:
:in:not_in:include:exclude
Note that :starts_with, :ends_with and :contains operations can be written using :like, but offer a more declarative style and are safer, as they escape the % and _ characters for you. You may also perform case insensitive searchs using these functions. Example:
QueryBuilder.where({:name, :starts_with, "jo"})QueryBuilder.where({:name, :starts_with, "jo", case: :insensitive}) # `:i` will also workWhen using :like or :ilike, make sure to escape % and _ characters properly.
You may also add OR clauses through QueryBuilder.where/4's fourth argument:
QueryBuilder.where(query, [], [name: "John"], or: [name: "Alice", age: 42], or: [name: "Bob"])QueryBuilder.maybe_where/3 and QueryBuilder.maybe_where/5
query
|> QueryBuilder.maybe_where(some_condition, name: "Alice")The above will run where/2 if the given condition is met.
QueryBuilder.order_by/2
QueryBuilder.order_by(query, asc: :lastname, asc: :firstname)QueryBuilder.order_by/3
QueryBuilder.order_by(query, :articles, asc: :title@articles)
`QueryBuilder.maybe_order_by/3` and `QueryBuilder.maybe_order_by/4`
```elixir
query
|> QueryBuilder.maybe_order_by(some_condition, asc: :lastname, asc: :firstname)
The above will run order_by/2 if the given condition is met.
QueryBuilder.preload/2
QueryBuilder.preload(query, [role: :permissions, articles: [:stars, comments: :user]])QueryBuilder.left_join/4
QueryBuilder.left_join(query, :articles, title@articles: "Foo", or: [title@articles: "Bar"])QueryBuilder.offset/2
QueryBuilder.offset(query, 10)QueryBuilder.limit/2
QueryBuilder.limit(query, 10)QueryBuilder.from_list/2
QueryBuilder.from_list(query, [
where: [name: "John", city: "Anytown"],
preload: [articles: :comments],
order_by: {:articles, asc: :title@articles},
limit: 20,
offset: 10
])The QueryBuilder.from_list/2 function was the main motivation behind the writing
of this library. As explained above, it allows to add querying options to the
Context functions. Example:
defmodule MyApp.Blog do
alias MyApp.Blog.Article
def get_article_by_id(id, opts \\ []) do
QueryBuilder.where(Article, id: id)
|> QueryBuilder.from_list(opts)
|> Repo.one!()
end
endThe function can now be called as follows (for instance, from a Controller):
Blog.get_article_by_id(id, preload: [:comments])
Blog.get_article_by_id(id, preload: [:likes])
Blog.get_article_by_id(
id,
order_by: {:comments, desc: :inserted_at@comments},
preload: [comments: :user]
)Extending Query Functions
In the event that you want to extend QueryBuilder's functionality to include custom app specific query functions, there's the QueryBuilder.Extension module to facilitate that. You can create a module with your app specific query functionality and use the QueryBuilder.Extension module to inject all QueryBuilder functions into your custom module. Any custom query functions added to your custom module are included in QueryBuilder.from_list/2.
For example:
defmodule MyApp.QueryBuilder do
use QueryBuilder.Extension
defmacro __using__(opts) do
quote do
require QueryBuilder
QueryBuilder.__using__(unquote(opts))
end
end
# Add app specific query functions here...
def where_initcap(query, field, value) do
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
query
|> where(&text_equals_condition.(field, value, &1))
end
end
defmodule MyApp.Accounts.User do
use MyApp.QueryBuilder
schema "users" do
field :name, :string
field :active, :boolean
end
end
defmodule MyApp.Accounts do
alias MyApp.QueryBuilder, as: Query
def list_users(opts \\ []) do
# Query list can include custom query functions as well:
# [where_initcap: {:name, "john"}, where: {:active, true}]
MyApp.Accounts.User
|> Query.from_list(opts)
|> Repo.all()
end
endSpecial Considerations
With the auto-binding functionality offered by Query Builder, you can specify field comparisons in queries using the special atom value syntax: :<field_name>@self. This way Query Builder understands the intent is to compare fields vs a raw value. For example:
users_where_name_matches_nickname =
User
|> QueryBuilder.where({:name, :eq, :nickname@self})
|> Repo.all()would resolve to a query along the following form:
select * from users where users.name = users.nicknameInstallation
Add query_builder for Elixir as a dependency in your mix.exs file:
def deps do
[
{:query_builder, "~> 1.4.2"}
]
endHexDocs
HexDocs documentation can be found at https://hexdocs.pm/query_builder.
