Fork me on GitHub

filters

An article by Gaspard Bucher

operators

The filters support the following operators:

+ - < <= = >= >
or and lt le eq ne ge gt
like 'not like' match in 'not in' is null

Use parenthesis to group clauses.

images where ((created_at < #{date}) and title like 'foo%') or type = 'Icon' in site

Make sure all referenced properties are indexed, otherwise they are not seen by database queries (unknown field error).

Also use parethesis for the “in” clause. The “in” clause even supports lists with items of mixed type (the following query does not make any sense but should give you an idea of what you can do).

images where flag in (#{params[:a]}, #{params[:b]}, '45', 23, "foo#{params[:c]}")

You may also pass an array to the “in” clause:

images where parent_id in (#{parent_selection_string.split(",")})

or / and filtering and query merging

As seen above, the “or” and “and” operators can be used within filter clauses (where … or …), but they can also be used to merge queries like this:

(images where title = 'foo') or (documents where created_at < now - 4 days) limit 5

If you need to use “select” within the merged queries, all queries need to be balanced (contain the same number of nested “from” queries) to workaround errors like missing ‘no1’, until the current merging code is generalized. Select is always possible on the outer clause (final result).

You may artificially “balance” queries by adding otherwise unnecessary fragments to shorter sub-queries. For examle, a simple

... or (searchedclass) or ...

may be extended like this (to have two “from” clauses):

... or (searchedclass where class like searchedclass from nodes where id = #{id} from nodes in site) or ...

(The redundant “where class like searchedclass” may only be neccessary due to a current bug that lets the query return nodes of all classes without this.)

Fulltext

You can use the special operator match to do a fulltext search on nodes, either using “fulltext” (mysql like on idx_text_high) or “sphinx” for sphinx search.

pages where fulltext match #{params[:q]} in site

With sphinx search:

pages where sphinx match #{params[:q]} in site

RubyLess

At some point, you will need to feed your filters with parameters from the url or other information in the current context. To do this, you simply use #{} and the content will be evaluated as RubyLess.

String interpolation:

pages where title like '#{params[:q]}%'

Direct value:

pages where event_at > #{current_date}

If such code makes you worry about SQL safety, you are right. String interpolation is very bad when you are in real SQL. Never do it. Here, however, we are in a sandboxed environment and such interpolations will produce proper bound variables without SQL injection risks.

We really hope that when enjoying the #{} syntax in sqliss, you will remember to not use the same type of code in non-safe environments (Ruby, Rails).

class / role filtering

These filters use the special fields “class” and “role”.

Find objects that are posts (not sub-classes):

nodes where class = Post

Find objects that are posts (with sub-classes):

nodes where class like Post

Find objects that have the ‘Task’ role:

nodes where role = Task

With the “class” filter, you can also negate the filters:

documents where class not like Image in site

relation filtering

This is possible by using the special ”.id method” on the relation name. For example, the following query finds all nodes that have the node 33 as “hot” object.

nodes where hot.id = 33 in site