Compose Ecto Query From Client

Image not Found

The story

At our company, OnPoint, we are building an ecommerce website using Phoenix Framework. And I am working on admin to manage product, orders … All the listing pages need a filter and this filter change frequently, operation team wants to add this field, order by that field. And each time they change their requirements I have to update query code.

My team use Django Admin before, they support an easy way to compose query directly from the URL. You don’t have to change code on the back-end. It’ll be great if I can do it with Phoenix.

What it should have:

  • Support basic query operator: >, >=, <, <=, =, !=, like, ilike, in
  • Query join table
  • Can sort result

This is the result after some night of work:

https://github.com/bluzky/querie

How it work

  • I define a simple rule for passing parameter from client side. The key must follow format [column]__[operator]=[value].
  • On the server side, it is parsed to {operator, {column, value}} with appropriate data type
  • Then it is passed to a filter function to build Ecto query dynamically

You can try with example project to see how it works.

How to use it

1. Define a filter schema

For example you have a Post schema:

 1defmodule Example.Content.Post do
 2  use Ecto.Schema
 3  import Ecto.Changeset
 4
 5  def state_enum(), do: ~w(draft published archived trash)
 6
 7  schema "posts" do
 8    field(:content, :string)
 9    field(:state, :string, default: "draft")
10    field(:title, :string)
11    field(:view_count, :integer, default: 0)
12    belongs_to(:category, Example.PostMeta.Category)
13    belongs_to(:author, Example.Account.User)
14  end
15end

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

1@schema %{
2    title: :string,
3    state: :string, # short form
4    view_count: [type: :integer] # long form
5}

2. Parse request parameters and build the query

Use Querie.parse/2 to parse request parameters with your schema

 1alias Example.Content.Post
 2
 3def index(conn, params) do
 4    with {:ok, filter} <- Querie.parse(@schema, params) do
 5	 query = Querie.filter(Post, filter)
 6	 # Or you can pass a query like this
 7	 # query = from(p in Post, where: ....)
 8	 # query = Querie.filter(query, filter)
 9	 posts = Repo.all(query)
10	 # do the rendering here
11    else
12    {:error, errors} ->
13	 IO.puts(inspect(errors)
14	 # or do anything with error
15	 # error is a list of tuple {field, message}
16    end
17end

3. Compose URL

Then from client side you can send a form:

 1<form action="/posts">
 2    <label>Titlte</label>
 3    <input type="text" name="title__icontains">
 4    <label>State</label>
 5    <select name="state">
 6        <option value="draft"></option>
 7        <option value="published"></option>
 8        <option value="trashed"></option>
 9    </select>
10    <label>View count greater than</label>
11    <input type="number" name="view_count__ge">
12</form>

Or directly from URL with data like this:

http://localhost:4000/posts?title__icontains=elixir&state=published&view_count__ge=100

Enter and see the result

Query joined table

It quite simple to filter result with filter on joined tables.

1. Update your query

Querie support ref operator to join tables. For example you want to query Post by author whose email contains sam the query would be:

?author__ref[email__icontains]=sam

2. Update your schema

 1alias Example.Account.User
 2
 3@schema %{
 4    title: :string,
 5    state: :string,
 6    view_count: [type: :integer],
 7    author: [
 8		type: :ref, # this references to another schema
 9		model: User, # which schema to query
10		schema: %{ # define filter schema for User
11			email: :string
12		}
13	  ]
14}

For more query options, please read document

If you have any suggestion, please leave a comment or open an issuse on Github.

Thanks for reading.