Filtering

This guide demonstrates use cases for the Filter Sieve. This sieve is used when a filter parameter is found in the query parameters, e.g. ?filter[name]=Rhino.

General usage

The canonical use case for the filter sieve is to query for results that have a certain field equal to a specific value. For example, if it is needed to fetch users that have the first name equal to Mary, the request and its query parameters could be:

/users?filter[first_name]=Mary

The general format is therefore using a filter parameter with the field name (first_name) between square brackets []. The value should not have quotes.

Comparison operators

The Filter sieve supports many comparison operators. They are all unary, meaning that they will always compare a column against the given value. The comparison method varies depending on the operator:

• gt: greater than
• lt: less than
• gteq: greater than or equal to
• lteq: less than or equal to
• eq: equal to
• *_coalesce: coalescing operators
• diff: different from
• tree_*: tree operators (see below)

In order to use an operation, one must chain them under the column name. For example, if it is needed to fetch blog posts created after June 30th 2002:

/blog_posts?filter[created_at][gt]=2002-06-30

Coalescing Operators

Coalescing operators are used to compare a column against a value and COALESCE the column to the value if the column is null. This is useful when a row should potentially be included in results if it has a null value. The available coalescing operators are:

• gt_coalesce: greater than
• lt_coalesce: less than
• gteq_coalesce: greater than or equal to
• lteq_coalesce: less than or equal to
• eq_coalesce: equal to

For example:

/blogs?filter[published_at][lteq_coalesce]=2023-12-31

Would get all blogs published before 2023-12-31, including blogs that have not been published yet (published_at is null).

Tree Operators

Tree operators can act on has_ancestry models (Rhino::Resource::ActiveRecordTree) and can execute any of the tree navigation commands from the node passed in. Commands act as per [https://github.com/stefankroes/ancestry#tree-navigation] and the available list is:

• tree_parent
• tree_root
• tree_ancestors
• tree_children
• tree_descendants
• tree_indirects
• tree_siblings
• tree_subtree
• tree_path

/blogs?filter[category][tree_subtree]=1

Null-aware operators

Using query params to compare fields to null is always tricky, because everything from query params is treated as text, so using:

/blog_posts?filter[title][eq]=null

would not compare the title to the null value, instead it would compare it to a string "null".

The is_null operator can be used to achieve explicit null comparison. Using true as argument makes the database query look for records that have a field equal to null. Using false, on the other hand, make the database query look for records that do not have a field equal to null.

Therefore,

/blog_posts?filter[title][is_null]=true

translates to something like

... WHERE title IS NULL

whereas

/blog_posts?filter[title][is_null]=false

translates to something like

... WHERE title IS NOT NULL

info

To see if a relationship is null, use id of the relationship as the field name name. For example, to see if a blog has no category, use filter[ca][category][id][is_null]=true.

Combining operators

Using more than one operator for a single field is supported, so searching for objects with a certain column in a range of two values is simple. In order to fetch all blog posts created between June 30th 2002 and July 8th 2014:

/blog_posts?filter[created_at][gt]=2002-06-30&[created_at][lt]=2014-07-08

Naturally, the operators applied to the same field will be combined with an AND operation.

Equality shorthand

If no operator is specified, the sieve will assume the equality operator eq. Therefore,

/users?filter[first_name]=Mary

is the same as

/users?filter[first_name][eq]=Mary

Relationships

This sieve can also detect referecend joined tables and compose a join clause with all the tables needed in order to query for nested tables attributes. For example, it is possible to fetch blog posts from a certain blog (using the blog's id) or to fetch all blog posts from any blog created before some specific date. Those queries could be, respectively:

/blog_posts?filter[blog]=1

and

/blog_posts?filter[blog][created_at][lteq]=1999-12-31

Belongs to

It is possible to fetch resources that belong specifically to another resource. For the case of blog posts, that each belong to blog, if a certain blog has id 1 and it is needed to get all blog posts from this blog:

/blog_posts?filter[blog]=1

The sieve engine will identify the relationship and assemble the query using the blog_id column from the blog_posts table. As id is an attribute just like any other else, one could use the more explicit forms:

/blog_posts?filter[blog_id]=1

or

/blog_posts?filter[blog][id]=1

or even

/blog_posts?filter[blog][id][eq]=1

Has many

Has many replationships works the same way as belong to. However, it is important to mind the correct name of the relationship, generally this kind of association uses the plural name of the related table. If blog posts had a 1:N relationship with, for example, a comments table, it would be possible to fetch posts that had at least one comment made by a user with id equal to 55:

/blog_posts?filter[comments][user]=55

or to fetch posts with at least one comment that doesn't have the content "Bad post.":

/blog_posts?filter[comments][content][diff]=Bad post.

Polymorphic relationships

In the case of blog posts and tags, there is a polymorphic many-to-many relationship.

class BlogPost < ApplicationRecord
  acts_as_taggable_on :tags
end

This doesn't differ from a regular has many, so one could issue requests like:

/blog_posts?filter[tags]=1

or

/blog_posts?filter[tags][name]=Social

Overridden relationship name

For the case of overridden names in relationships like:

class Blog < ApplicationRecord
  belongs_to :author, default: -> { Rhino::Current.user }, class_name: 'User', foreign_key: :user_id
end

The the user field should be address by its alias author, as in:

/blogs?filter[author]=42

or

/blogs?filter[author][name]=Rhino

Relationship operators

The Filter sieve also supports some relationship operators that build up a very specific query, touching not only the WHERE clause, but also other parts of the query, like the JOIN clause.

For example, given a 1:N relationship between blog posts and og meta tags, in which a blog post can have multiple tags, one could have the need of fetching the blog post records that don't have any tags associated. One way of doing that is using OUTER JOIN to join the blog_posts and og_meta_tags tables. That would match all blog posts and each of their associated tags, so the blog post records that don't have any tags would still be included, but with null values for the tag's columns. In order to keep only the blog posts records that are not tagged, one would only need to add a WHERE clause with og_meta_tags.id IS NULL.

For that, Rhino has the _is_empty association operator. The filter value in the query params would look like this:

/blog_posts?filter[og_meta_tags][_is_empty]=true

These operators can be combined with the other operators for the same table and for different tables as well:

/blog_posts?filter[og_meta_tags][_is_empty]=true&filter[og_meta_tags][name]=Urgent&filter[blog]=1