Creative Commons License
Tesseract Tutorial: Advanced Filtering by François Suter is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.
Based on a work at www.typo3-tesseract.com.

Support the project

If you appreciate what the Tesseract project does for you, consider donating to it.

Scenario

In the previous tutorial, we started using Data Filters. We introduced two filters, one to restrict the list of BE users to administrators, the other to non-administrators. In this tutorial we will explore another way to achieve this, without having two different Data Filters.

As we are expecting our list of BE users to grow significantly, we also want to introduce a search feature based on name or e-mail.

Preparing for work

Just make sure that you have been through the "Getting Started" and the "Introducing filters" tutorials, so that you have all pages and components expected.

Contexts

The extension "context" provides a framework for defining values using TypoScript that can be easily retrieved using Data Filters. Because they are based on TypoScript, these values can be inherited along branches in the page tree, making it possible to define values for a whole section of the site, i.e. "contexts".

In the second tutorial we split our list of BE users between administrators and non-administrators. This led to creating two Data Filters, one for each value of the "admin" flag. We can improve this by defining a "admin/non-admin" context and use only a single filter by referring to the context's value.

The context is just TypoScript, so we create a template on the "Web site administrators" page and put the following in the setup field:

 

config.tx_context {
    adminFlag = 1
}

 

On the "Web site editors" page, we do the same and just change the flag's value:

 

config.tx_context {
    adminFlag = 0
}

 

Now we can change our filter to:

 

admin = extra:adminFlag

 

The values defined by the context's TypoScript are available easily using the "extra:" key in the filter. With this enhancement, our filter has become "universal" and can be used in both Display Controllers. This enhances the reuse of components inside our web site.

A further enhancement could be to use a TypoScript constant instead of an explicit value in our context definition. That way the context itself would be defined only once and only the TypoScript constant needs to be changed.

Setting up the search form

The above example might seem a bit silly, but such reuse becomes more important as filters grow in complexity. So let's make it more complex! Imagine that we have far more users than the few defined by the Introduction Package. It would then become useful to be able to search the users database.

Tesseract does not provide any tool for building search forms, but the Data Filters can grab values from a wide variety of sources. This makes it possible to build forms with pretty much whatever tool we want, such as any of the form-building extensions available in the TER, some custom plug-in or even TYPO3's standard mail form! We are actually going to use the latter to exemplify that nothing fancy is needed.

Let's work on the "Web site editors" page, as the administrators page is not very interesting, with just one user listed. We need only the simplest of forms: a single input field to enter some seach text is enough. Here's the setup of the "Mail Form" content element:

and here's how it looks on the page:

Advanced filtering

The next step is to change our filter to use the search data. Let's set up the filter to search just on the user's real name. This is rather trivial. All we need is to add a single line to our filter:

 

realName like gp:name

 

What does this all mean?

  1. the first part says that we are putting a condition on the "realName" field
  2. the second part is the operator. In SQL this will be interpreted as a LIKE condition. Furthermore the value to be tested will be surrounded with "%", making it a partial word search (this comes simply from how extension "dataquery" interprets the "like" operator from the Data Filter).
  3. the value to test will be taken from a GET/POST variable (the "gp:" key from the expression) called "name" (which is how our search field is called).

We are now ready to perform a first search. Type "news" in the search field and submit. You should see the following result:

Great, we're done, aren't we? Not quite, there's a bit more to search than this.

Caching

Try another search: the result stays the same. The reason for this is that the Display Controller content element is cached. Hence the whole page is cached and nothing changes when you change the search term. The solution is to change the Display Controller to its non-cached version.

We are still not done. Try leaving the search field empty and hitting the "Search" button again. The previous search result stays, although the Display Controller is not cached. What it at work here is the internal caching of the Display Controller. When a filter is evaluated it is stored into the session cache. This is very convenient when just one of the filtering parameters changes, but not the others. This is used in particular when paginating search results: the search criteria themselves must stay untouched, only the page must change.

The drawback of this is that if some value expected in the filter is not available (e.g. the expected GET/POST variable was not submitted or was empty), the value from the cache is used. To avoid this one can place a hidden field called "clear_cache" in the search form. This ensure that when the "Search" button is actually hit, the filter is cleared and a new one is entirely evaluated. This will not happen while paginating, because the pagination links will (should) not include a "clear_cache" variable.

Thus our form setup is changed to:

Leaving the search field empty will now produce the list of all users.

Improving the filter

The search feature works but is not so useful yet. It can search only on the users' real names, but some miss that information. It would be better to search also on the username and the e-mail address.

This might seem trivial, but we stumble on a limit of the current implementation of Data Filters: all conditions apply together either with AND or OR logical operators, but not a mix. This is not good for our example, where we want the search text to match either the real name or the user name or the e-mail address, while still (and) enforce the admin/non-admin flag. This limitation of Data Filters was a choice made for keeping things more simple from a coding point of view as well as from a usage point of view.

There are two ways to work around this limitation:

  1. use conditions on the real name, username and e-mail address, together with an OR operator, and move the condition on the admin flag to the "Additional SQL" field.
  2. concatenate the real name, username and e-mail fields and apply the condition of that result and keep the admin/non-admin flag condition.

The first solution is acceptable because the admin/non-admin flag is always present. Indeed one of the main advantages of Data Filters (at least as they are implemented in extension "datafilter") is that conditions are ignored if the values they are keyed to are empty. On the other hand, the additional SQL field will always be used by "dataquery" even if it has some expressions inside that evaluate to empty, which may lead to invalid SQL. There is no risk of this happening with the admin/non-admin flag as it is always defined.

We will look in more details only for the second solution, as it touches on an interesting feature regarding aliases.

Let's rewrite our SQL query like:

 

SELECT username, realName, email,
CONCAT(username, ' ', realName, ' ', email) AS fullname
FROM be_users

 

Now we can apply the filter to the "fullname" field, e.g.:

 

fullname like gp:name

 

The interesting thing is that Data Query will silently work around the limitation that you cannot use aliases directly in the WHERE clause. Indeed the following query:

 

SELECT username, realName, email,
CONCAT(username, ' ', realName, ' ', email) AS fullname
FROM be_users WHERE fullname LIKE '%simple%'

 

will throw an error. Instead Data Query will match the alias to the function and replace it automatically, thus issuing in fact the following query:

 

SELECT username, realName, email,
CONCAT(username, ' ', realName, ' ', email) AS fullname
FROM be_users WHERE CONCAT(username, ' ', realName, ' ', email)
LIKE '%simple%'

 

which is correct.

As an exercise, apply the changes mentioned in this section and you should have a fully-working search.