DeveloperTutorialsOther

Database Filters

Database Filters

Filters let you control data when working with dynamic elements. They are commonly used with templating tags in a page's markup, but can also be used in PHP scripts.


<ss:data:repeater datafilter="array(array('page_is_hidden_in_navigation', '=', 0))" parsenow="datafilter" datasource="\Components\Website\Pages::getAll()" as="arrPage">
<h2>[? $arrPage['page_title'] ?]</h2>
</ss:data:repeater>

$arrVisiblePages = \Components\Website\Pages::getAll(array('filter' => array(array('page_is_hidden_in_navigation', '=', 1))));

Filter Structure

Filters take the form of PHP arrays, and are structured so that they can be read left-to-right (or top-to-bottom, as with the following examples). The following examples should illustrate how their structure allows for both simple and complex filters:

Show me pages that are visible in navigation:

array(
array('page_is_hidden_in_navigation', '=', '0')
)

Show me pages that are visible in navigation and have a page icon:

array(
array('page_is_hidden_in_navigation', '=', '0'),
'AND',
array('page_icon', '!=', null)
)

Order Of Operations

When writing complex conditions that contain both AND and OR operations, it's essential to be aware of the priority in which they are evaluated.

a AND b OR c AND d

The AND's are grouped before the OR's are grouped, similar to multiplication having a higher priority than addition in algebra:

(a * b) + (c * d)

(a AND b) OR (c AND d)

But what if we changed our condition to look like the following?

a OR b AND c OR d

According to the default order of operations, the condition would be evaluated as follows:

a OR (b AND c) OR d

If that's not what we want, we must change how our condition is grouped:

(a OR b) AND (c OR d)

Here is an example showing the array structure for a grouped condition:

array(
array(
array('data_a', '=', '1'),
'OR',
array('data_b', '=', '1')
),
'AND',
array(
array('data_c', '=', '1'),
'OR',
array('data_d', '=', '1')
)
)

Differences Between XML Control and PHP Filters

When defining filters using the datafilter attribute in your markup, you only need to worry about the filter condition array. However functions in the PHP API often expect a Properties Array, which contains a Filter Array plus extra properties.

Filter Reference

Comparators

<
Less than
Compare numbers, dates and strings.
<=
Less than or equal to
Compare numbers, dates and strings.
>
Greater than
Compare numbers, dates and strings.
>=
Greater than or equal to
Compare numbers, dates and strings.
=
Equal to
The values must match exactly.
!=
Not equal to
The values must not match.
LIKE
String similar to
Match patterns. For example, 'contact_first_name', 'LIKE', 'J%', will match on all people with a first name beginning with the letter 'J'.
NOT LIKE
String not similar to
Exclude patterns. For example, 'contact_postcode', 'NOT LIKE', '4%', will exclude all people with a postcode beginning with four.
IN
Equal to any of the supplied values
Match on multiple possible values. For example, 'design_id', 'IN', array(1, 2, 10), to match pages with 1 of multiple designs.
NOT IN
Not equal to any of the supplied values
Exclude multiple values. For example, 'contact_postcode', 'NOT IN', array(1001, 1002), to exclude Contacts registered in the specified post codes.

Boolean Operators
AND
All of the filter conditions must be true for each row.
OR
At least one of the filter conditions must be true for each row.

Share This Tutorial: