Compose Ecto query from the client side

Install

Add to your mix.exs file:

[
	{:querie, "~> 1.0"}
]

This is what Query does

It turns this:

http://localhost:4000/products?name__icontains=milk&price__ge=32&rating__sort=desc

Into this

from(p in Product, where: ilike(p.name, "%milk%") and p.price >= 32, order_by: [desc: :rating])

Cool, right?

Table of content

• What is Querie?
• What Querie can do?
• How to use Querie?
  • Install
  • Query on a single table
  • Sort query result
  • Query between
  • Join tables
  • Custom join field
• Build filter in your code
• Supported operators

What is Querie?

Querie is a library that help you to build the query directly from the URL parameters without writing to much code. If you want to add more filter criteria? Don't worry, you only need to change the filter schema.

What Querie can do?

• Build Ecto Query dynamically
• Query reference tables
• Support common query operator: > >= < <= = not like ilike between is_nil
• Support sort query

Especially Querie does not use macro 😇

How to use Querie?

Query on a single table

There are 3 steps to make it work 1. Define a filter schema

Schema is a map which define:* data type of field, so it can be parsed correctly

• which field can be filter, other extra fields are skip
• which tables are referenced and how to query referenced tables

For example you have a Post schema:

defmodule Example.Content.Post do
  use Ecto.Schema
  import Ecto.Changeset

  def state_enum(), do: ~w(draft published archived trash)

  schema "posts" do
    field(:content, :string)
    field(:state, :string, default: "draft")
    field(:title, :string)
    field(:view_count, :integer, default: 0)
    belongs_to(:category, Example.PostMeta.Category)
    belongs_to(:author, Example.Account.User)
  end
end

And you want to filter the Post by title, state, view_count. This is the schema:

@schema %{
    title: :string,
    state: :string, # short form
    view_count: [type: :integer] # long form
}

2. Parse request parameters and build the query Use Querie.parse/2 to parse request parameters with your schema

alias Example.Content.Post

def index(conn, params) do
    with {:ok, filter} <- Querie.parse(@schema, params) do
	 query = Querie.filter(Post, filter)
	 # Or you can pass a query like this
	 # query = from(p in Post, where: ....)
	 # query = Querie.filter(query, filter)
	 posts = Repo.all(query)
	 # do the rendering here
    else
    {:error, errors} ->
	 IO.puts(inspect(errors)
	 # or do anything with error
	 # error is a list of tuple {field, message}
    end
end

3. Build the URL query Parameter must follow this format: [field_name]__[operator]=[value]. If no operator is specified, by defaut = operator is used. Supported operators are listed below.

For example you want to filter Post which:

• title contains elixir
• state is published
• view_count >= 100

URL query string would be: ?title__icontains=elixir&state=published&view_count__ge=100

Sort query result

Follow this format to sort by field: <field>__sort=<asc|desc>

For example you want to sort by title ascending, add this to query: title__sort=asc

Simple, right?

You can set default sort order and sort priority for each field:

%{
    view_count: [type: :integer, sort_default: :desc, sort_priority: 1]
    title: [type: :string, sort_default: :asc, sort_priority: 2]
}

Field with smaller sort_priority smaller is sorted first

Query between

Query supports query between min and max value. It translates between to > min and < max. And inclusive version is ibetween which translated to >= min and <= max

You don’t have to modify your schema to use between. From client you can send between value in 3 forms:

• value with separator: view_count__between=20,60
• array of 2 value: view_count__between[]=20&view_count__between[]=60
• map value with min and max: view_count__between[min]=20&view_count__between[max]=60

If min or max is omitted, it will use one compare operator.

Query reference tables

For example, the Post schema above references to 2 other schemas: User and Category you can filter with conditions on those 2 schema.

This is the schema for User

defmodule Example.Account.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field(:email, :string)
    field(:first_name, :string)
    field(:last_name, :string)
  end

1. Update your schema

alias Example.Account.User

@schema %{
    title: :string,
    state: :string,
    view_count: [type: :integer],
    author: [
		type: :ref, # this references to another schema
		model: User, # which schema to query
		schema: %{ # define filter schema for User
			email: :string
		}
	  ]
}

2. Update your query For example you want to query Post by author whose email contains sam the query would be: ?author__ref[email__icontains]=sam

Custom join field

You can specify custom join field with 2 options:

• foreign_key default is [field]_id. In the example above, it is author_id
• references is the key to join on the other table. Default is id

Filter directly from your code

You can build filter for Querie directly from your code

filters = %{
    title: {:ilike, "elixir"},
    tag: "elixir", # query with = operator,
    category_id: [1, 2], # query with in operator,
    view_count: {:between, [10, 50]}
}

Querie.filter(Post, filters}

Supported operators

This is list of supported operators with mapping key word.

operator	mapping keyword
=	is or omit
!=	ne
>	gt
>=	ge
<	lt
<=	le
like	contains or like
ilike	icontains or ilike
between	between
inclusive between	ibetween