Querie - Compose Ecto query from the client side

Chat / Discussions
, , ,
1

I have just release first version of 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.

Github project here https://github.com/bluzky/querie

This is what it can do

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

Here is 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 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

If you have any suggestion, please comment.
Thanks.

6 Likes