Filter Querying is a methods of reducing the amount of records that appear
in a selection list by selecting from a sub query first.
This is possible the most flexible of the filtering methods and requires the least
processing on the server side
Note: that at this time filtering only works on the Search list and no other selection
lists.
As with all filtering, to set up filter groups requires making a call to
the setFilterQuery method after creating the selection list.
To see how to setup a filter query lets look at the example.
In our example we have a person form and we want to filter the search list by a subquery on the access table via the person.accessid.
Firstly we create the search list:
$dbForm->setSearchField("personid","select personid, lastname, firstname from person
where canceldate is null and (1=1) order by lastname, firstname", ", ",
"Select a Person", true);
Note that we have included (1=1) in the query. This is a replacement tag that will be replaced with a valid where clause during the filter process.
Next we need to call setFilterQuery
$dbForm->setFilterQuery("personid","accessid","select accessid, name from access
order by name","(1=1)","","Select an Access Filter");
Lets now look at this to see what it is doing
$field = "personid" which tells setFilterQuery to filter the personid field.
$filterField = "accessid" which tells setFilterQuery to use accessid as the field
to replace in the search query.
$filterQuery = "select accessid, name from access order by name" which tells
setFilterQuery to use the given query for the filter groups.
$filterReplace = "(1=1)" which tells setFilterQuery to replace (1=1) in the search query
with a where clause on the $filterField field (i.e. accessid)
$glue = "" which tells setFilterQuery to not use any glue.
$defaultText = "Select an Access Filter" which tells setFilterQuery to display this text
at the start of the list.
When run this example will display a filter list consisting of the default text and the
records from the filter query, then once a group is selected the SearchField query will
be run with the filterReplace tag replaced with a where clause and a reset filter item is
added to the end of the list.
i.e. if accessid of 1 is select then the Search Field query will look as follows
select personid, lastname, firstname from person
where canceldate is null and (accessid=1) order by lastname, firstname
View source code for demo6.php
The template for this demonstration is fairly straight forward however it does use a new feature introduced in dbForm 0.8.2. It has the ability to incorporate attributes in the dbForm tags.
For example if we want a field to be displayed with a class and a specific style we can now incorporate that into the template rather than having to specify it in code. This makes the templates as flexible as possible.
In this example we are adding a class and style to a named field tag
{:field="firstname" class="content2" style="width:140px;"}
This will be replaced in the output html as something like:
<input type="text" name="firstname" value="Tony" class="content2" style="width:140px;" />
This allows almost all formatting to be controlled via the template if required.
View source code for demo6.tpl
The files that have been used in this demonstration can be downloaded so you can try them out for yourself.