Better support for complex filters

[Gaspard Bucher]

Hi there !

As part of the upcoming 1.2.3 release, I have updated QueryBuilder to support more complex filters and null testing.

For example if you want to find all time entries that are not linked to an invoice, you can now use (TimeEntry class has an assigned_invoice relation):

time_entries where assigned_invoice.id is null in site

You can also mix clauses if you want the time entries in a given invoice or not in any invoice:

time_entries where assigned_invoice.id is null or assigned_invoice.id = #{main.id} in site

Note that there is a lot of magic going on here (id to zip transformation, finding the proper relation, etc). The resulting SQL looks like this:

SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` 

FROM nodes

  LEFT JOIN links ON links.source_id = nodes.id AND links.relation_id = 126629661

  LEFT JOIN nodes AS jn1 ON jn1.id = links.target_id

WHERE #{secure_scope('nodes')} AND (jn1.zip IS NULL OR jn1.zip = ?) AND nodes.kpath LIKE 'NPP%'

GROUP BY nodes.id

ORDER BY nodes.zip ASC

Also note that when a relation is used, the "link_id" is part of the select so that the link can easily be "unlinked" with <r:unlink/>.

Cheers

                                                               Gaspard