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:
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 filter 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. Compose URL
Then from client side you can send a form:
<form action="/posts">
<label>Titlte</label>
<input type="text" name="title__icontains">
<label>State</label>
<select name="state">
<option value="draft"></option>
<option value="published"></option>
<option value="trashed"></option>
</select>
<label>View count greater than</label>
<input type="number" name="view_count__ge">
</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
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
}
]
}
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.
Top comments (0)