Filtery
Filtery
help you to build the query using a syntax which is similar to Mongo
This is super useful when you want to build filter from request params.
filter = %{
status: "active",
email: {:not, nil},
role: ["admin", "moderator"]
}
Filtery.apply(User, filter)
Installation
Add filtery
to your list of dependencies in mix.exs
:
def deps do
[
{:filtery, "~> 0.2"}
]
end
Documentation is published here https://hexdocs.pm/filtery.
Table of Contents
- Installation
- I. Usage
- II. Syntax
- III. Supported operator
- IV. Define your operators
- V. Joining tables <!-- markdown-toc end -->
I. Usage
Filtery
help you to build the query using a similar syntax with MongoDB like this:
filter = %{
status: "active",
email: {:not, nil},
role: ["admin", "moderator"]
}
Filtery.apply(User, filter)
The result is a query like this:
from(u in User, where: u.status == "active" and not is_nil(u.email) and u.role in ["admin", "moderator"])
II. Syntax
You can use <field>: <value>
expressions to specify the equality condition and query operator expressions.
%{
<field1>: <value1>,
<field2>: { <operator>, <value> },
...
}
Notes: all operator belows are reserved keywords and cannot be used as field name
III. Supported operator
1. Comparition operator
:eq | Matches values that are equal to a specified value. |
---|---|
:gt | Matches values that are greater than a specified value. |
:gte | Matches values that are greater than or equal to a specified value. |
:in | Matches any of the values specified in an array. |
:lt | Matches values that are less than a specified value. |
:lte | Matches values that are less than or equal to a specified value. |
:ne | Matches all values that are not equal to a specified value. |
:nin | Matches none of the values specified in an array. |
2. Logical operator
Name | Description |
---|---|
:and | Joins query clauses with a logical AND returns all documents that match the conditions of both clauses. |
:not | Inverts the effect of a query expression and returns documents that do not match the query expression. |
:or | Joins query clauses with a logical OR returns all documents that match the conditions of either clause. |
AND
operator
By default, if a map or keyword list is given, Filtery
will join all field condition of that map using AND
Filtery.apply(User, %{status: "active", age: {:gt, 20}})
# same with
Filtery.apply(User, %{and:
%{status: "active", age: {:gt, 20}}
})
# same with
Filtery.apply(User, %{and:
[status: "active", age: {:gt, 20]}
})
# same with
from(u in User, where: u.status == "active" and u.age > 20)
OR
operator
The :or
operator performs a logical OR
operation on an array of two or more <expressions>
Filtery.apply(Product, %{or: %{
price: {:gt, 20},
category: "sport"
}})
NOT
operator
Performs a logical NOT
operation on the specified <operator-expression>
Syntax: %{ field: %{ not: <operator-expression> } }
Filtery.apply(Product, %{or: %{
price: {:gt, 20},
category: {:not: "sport"}
}})
Filtery.apply(Product, %{or: %{
price: {:not, {:gt, 20}},
category: "sport"
}})
3. Extra operator
Filtery
provides some more useful operators to work with text and range.
Name | Description |
---|---|
:between | Matches values > lower bound and < upper bound |
:ibetween | Matches values >= lower bound and <= upper bound |
like , contains | Match values which contains specific value |
ilike , icontains | Case insensitive version of like |
has | For array type column, Matches array which has specific value |
Syntax
between
|ibetween
Syntax:
field: {:between, [lower_value, upper_value]}
Check NULL
and skip nil
filter
By default is a value in the filter is nil
, Filtery
applies is_nil
to check NULL
value. You can tell Filtery
to ignore all nil
field by passing skip_nil: true
to the options
Filtery.apply(query, filter, skip_nil: true)
In that case, if you want to check field which is NULL
or NOT NULL
you use :is_nil
instead of nil
when passing value to the filter:
Filter.apply(query, %{email: :is_nil}, skip_nil: true)
IV. Define your operators
You can extend Filtery
and define your own operator. For example, here I define a new operatory equal
defmodule MyFiltery do
use Filtery.Base
def filter(column, {:equal, value}) do
dynamic([q], field(q, ^column) == ^value)
end
end
To support a filter, you must follow this spec
@spec filter(column::atom(), {operator::atom(), value::any()}) :: Ecto.Query.dynamic()
Within the body of filter/2
function using dynamic
to compose your condtion and return a dynamic
V. Joining tables
Filtery
defines a special operator ref
to join table
Syntax: <field>: {:ref, <qualifier>, <filter on joined table>}
If qualifier
is skipped, then :inner
join is used by default.
query = Filtery.apply(Post, %{comments: {:ref, %{
approved: true,
content: {:like, "filtery"}
}}})
And then you can use Name binding to do further query
query = where(query, [comments: c], c.published_at > ^xday_ago)
Qualifiers
By default Filtery
join using :inner
qualifier. You can use one of `:inner
, :left
, :right
, :cross
, :full
, :inner_lateral
or :left_lateral
qualifier as defined by Ecto.
### You can filter with nested ref
elixir Filtery.apply(Post, %{comments: {:ref, %{ approved: true, user: {:ref, %{ name: {:like, "Tom"} }} }}})
### Important Notes on ref
operator
- Field name must be the association name in your schema because Filtery
use assoc
to build join query.
In the above example, Post
schema must define association has_many: :comments, Comment
- Not allow 2 ref with same name because the name is used as alias :as
in join query, so it can only use one.