Fork me on GitHub


An article by Gaspard Bucher

Transform a hash of key/values into a pseudo sql filter

Transform a hash of key/values into a pseudo sql filter

see query


This simple method let’s you create search forms that can be used to search records by typing search criteria in the fields (same as FileMaker/Access search).


Imagine you want to search through a catalogue of cellos and find the ones costing less the 5’000$. With query_parse, you can prepare a query and let users use the form like this:



They just leave some fields blank and enter some special signs to make the query. The signs are:

code meaning
< 50 less then 50
> 40 more then 40
34 exactly ‘34’
DQ* starts with ‘DQ’
= #{params[:f]} equals some other param (uses RubyLess)
"DQ*" exactly equal to ‘DQ*’
null field is NULL
100..1000 value between 100 and 1000
25.3.2010 uses ‘datetime’ format to parse date
depending on visitor lang
25.3.2010..15.5.2009 value between 2010-03-25 and 2009-05-15
>10/25/1998 valid for an english visitor
!34 not equal ‘34’
!null not null
!1.1.2000..31.12.2000 not in the date interval
!foo* does not start with “foo”


Here is an example of zafu code containing both the form and the query:

<form action='#{url}'>price: <input name='filter[price]'/><br/>
year: <input name='filter[year]'/></form>

<ul do='query' select='cellos where #{query_parse(params[:filter])} in site limit 10'>
  <li do='each'>price: <r:price/> year: <r:year/></li>

For this to work, you need the ‘price’ and ‘year’ attributes of the Cello class to be indexed.

You need to use use “query_parse” inside a dynamic query (compiled during runtime) since the number and type of the arguments are only now on runtime. You can use params in a regular query, but not ‘query_parse’: <ul do='pages where title like "%#{params[:f]}%" in site'>.

An example that should work with zenas default setup:

<form action='#{url}'>
	title: <input name='filter[title]'/><br/>
	created: <input name='filter[created_at]'/><br/>
	<r:input type='submit'/>

<ul do='query' default='nodes' select='nodes where #{query_parse(params[:filter])} limit 10'>
  <li do='each'>title: <r:title/> created: <r:created_at/></li>
  <li do='elsif' test='query_errors' do='query_errors'/>
  <li do='else' do='t'>nothing found</li>

how this works

With query, the pseudo sql is only resolved on runtime. During resolution, the ‘text’ parameter is evaluated and produces cellos where price < 5000 in site limit 10. This query is then compiled and executed.

This feature in conjunction with scope index gives a very fast and powerful search tool.