DEPRECATION WARNING

This documentation is not using the current rendering mechanism and is probably outdated. The extension maintainer should switch to the new system. Details on how to use the rendering mechanism can be found here.

Queries and Data Filters¶

The main idea that underlies the whole Tesseract concept is to have libraries of elements that can be reused and combined in different ways. Part of this flexibility comes from the use of Data Filters. Via a controller (like the Display Controller) a given Data Query can be set in relation with a Data Filter. This makes it possible to reuse a Data Query and change it dynamically via the filters.

Technically the filter structure created by the Data Filter is passed to the Data Query by the controller using the \Tesseract\Tesseract\Service\ConsumerBase::setDataFilter::setDataFilter() method from the base provider interface. The filter structure is then translated into SQL by Data Query and added to the base query from the “SQL query” field.

Using aliases in filters¶

It is normally not possible to use aliases in the WHERE clause. However Data Query will recognize aliases used in Data Filters and map them to the original field they represented. Imagine the following query:

SELECT FROM_UNIXTIME(tstamp, '%Y') AS year FROM tt_content

with the following Data Filter:

year = date:Y

(which would select all content element edited during the current year). This will be (correctly) interpreted as:

SELECT FROM_UNIXTIME(tstamp, '%Y') AS year FROM tt_content WHERE (FROM_UNIXTIME(tstamp, '%Y') = 2010)

(assuming the current year is 2010), instead of:

SELECT FROM_UNIXTIME(tstamp, '%Y') AS year FROM tt_content WHERE (year = 2010)

which would cause a SQL syntax error.

Array values from filters¶

Imagine setting up a group of checkboxes like:

<input type="checkbox" name="tx_myext[foo][]" value="bob" />
<input type="checkbox" name="tx_myext[foo][]" value="alice" />

Next imagine a filter like:

fe_users.name like gp:tx_myext|foo

The value returned will be an array. This is handled by Data Query by creating a LIKE condition for each value and concatenating all these conditions with a “OR” logical operator. So the above example would result in the following SQL condition (assuming both checkboxes were checked):

(fe_users.name LIKE '%bob%' OR fe_users.name LIKE '%alice%')

It’s not possible to change the logical operator to “AND” (this didn’t seem useful after thinking quite a bit about it; the whole reasoning is outside of the scope of this manual; if you have a use case for this, please open a feature request on Forge).