ExSieve is meant to be used by a module implementing Ecto.Repo behaviour.

When used, optional configuration parameters can be provided. For details about cofngiuration parameters see ExSieve.Config.t/0.

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app
  use ExSieve
end

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app
  use ExSieve, ignore_erros: true
end

When use is called, a ExSieve.filter/3 function is defined in the Repo.

This function can and used for filtering entries based on query parameters

def index(conn, %{"q"=> params}) do
  posts = MyApp.Repo.filter(MyApp.Post, params)
  render conn, :index, posts: posts
end

Options can be overridden by setting on per-schema basis (see ExSieve.Schema) or on single ExSieve.filter/3 calls.

Examples

In the following we assume these schemas are defined in your application:

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    has_many :comments, MyApp.Comment

    field :title
    field :body
    field :published, :boolean

    timestamps()
  end
end


defmodule MyApp.Comment do
  use Ecto.Schema

  schema "comments" do
    belongs_to :post, MyApp.Post

    field :body

    timestamps()
  end
end

Simple query

Given this json representation of the query

{
  "m": "or",
  "id_in": [1, 2],
  "title_and_body_cont": "text",
  "comments_body_eq": "body",
  "s": ["title desc", "inserted_at asc"]
}

the following SQL query is sent to the database

SELECT posts.* FROM posts INNER JOIN comments ON posts.id = comments.post_id
  WHERE posts.id IN (1, 2)
  OR (posts.title ILIKE '%text%' AND posts.body ILIKE '%text%')
  OR comments.body == "body"
  ORDER BY posts.title DESC, posts.inserted_at ASC;

Grouping queries

Query fields can be nested for obtaining more advanced filters.

Given this json representation of the query

{
  "m": "and",
  "id_in": [1, 2],
  "g": [
    {
      "m": "or",
      "c": {
        "title_and_body_cont": "text",
        "comments_body_eq": "body"
      }
    }
  ],
  "s": ["title desc", "inserted_at asc"]
}

the following SQL query is sent to the database

SELECT posts.* FROM posts INNER JOIN comments ON posts.id = comments.post_id
  WHERE posts.id IN (1, 2)
  AND (
    (posts.title ILIKE '%text%' AND posts.body ILIKE '%text%')
    OR comments.body == "body")
  ORDER BY posts.title DESC, posts.inserted_at ASC;

Supported predicates

Base predicates

• eq
• not_eq
• cont
• not_cont
• lt
• lteq
• gt
• gteq
• in
• not_in
• matches
• does_not_match
• start
• not_start
• end
• not_end
• true
• not_true
• false
• not_false
• present
• blank
• null
• not_null

Composite predicates

• eq_any
• not_eq_all
• cont_all
• cont_any
• not_cont_all
• not_cont_any
• matches_all
• matches_any
• does_not_match_all
• does_not_match_any
• start_any
• not_start_all
• end_any
• not_end_all

Combinators

• or
• and

Custom predicates

ExSieve allows to define user-specific predicates.

These predicates must be defined at compile time with the :custom_predicates key of the :ex_sieve application environment. It should be a keyword list that maps predicate_names (atom) to Ecto.Query.API.fragment/1 strings.

config :ex_sieve,
  custom_predicates: [
    has_key: "? \\? ?",
    less_than_6: "? < 6",
    key_is: "(? ->> ?) = ?"
  ]

The first argument given to the fragment is the field while next ones are the values given in the query string.

Given this json representation of the query

{
  "metadata_has_key": "tag",
  "score_less_than_6": true,
  "metadata_key_is: ["status", "approved"]
}

the following SQL query is sent to the database

SELECT posts.* FROM posts
  WHERE posts.metadata ? 'tag'
  AND posts.score < 6
  AND (posts.metadata ->> 'status') = 'approved';

Predicate aliases

Aliases to built-in and custom predicates can be configured in using the :predicate_aliases key in :ex_sieve application environment. It should be a keyword list that maps an alias name (atom) to a predicate (atom).

Aliases for not existent predicates are silently discarded, multiple aliases can be defined for the same predicate.

config :ex_sieve,
  custom_predicates: [
    has_key: "? \\? ?"
  ],
  predicate_aliases: [
    m: :matches,
    hk: :has_key
  ]

Notes

LIKE injection

LIKE queries can suffer of LIKE injection attacks.

For this reason all predicates which result in a LIKE query (cont, not_cont, start, not_start, end, not_end and their composite predicates) are properly escaped.

Some exceptions are matches, does_not_match and their composite predicates that allows %, _ and \ chars in the value. You should be very careful when allowing an external user to use these predicates.