Fork me on GitHub

query_parse

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

presentation

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).

example

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:

Cellos:


price
year

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
dates  
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
not  
!34 not equal ‘34’
!null not null
!1.1.2000..31.12.2000 not in the date interval
!foo* does not start with “foo”

usage

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>
</ul>

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'/>
</form>

<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>
</ul>

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.