Working with filters programatically
This is a multi-part tutorial on how to use filters in AppGini. Filters are a powerful means of searching tables. This tutorial explains how they work, and shows how they can be controlled programatically. In part 1, we'll understand how to apply a default filter to a table.
AppGini supports up to 80 filters grouped into 20 groups. The filters page exposes only 12
filters grouped into 3 groups. But this is still enough to create complex searches. For
example, the filters defined in the screenshot to the right create a sophisticated search
of the orders table in the
Northwind online demo. Here is a quick
preview of the filters page in action
(click the "Apply Filters" button to view the filtered orders.)
So, for the example filters to the right, the generated SQL query would be structured like this:
( Filter 01 OR Filter 02 ) AND (Filter 05 OR Filter 06) AND (Filter 09 AND Filter 10)
Each filter has an index that identifies it. The first filter has an index of 1, the second one has an index of 2, and so on. We could define up to 80 filters (but that could possibly slow down our queries). Each filter is composed of 4 variables, as outlined in the table below.
Applying a default filter on a table
[1] In this tutorial, I'm assuming the date format specified in your AppGini project is "Month, day, year" with a Slash separator. If you're using a different format, you should change the code above to reflect that format.
What this tutorial is and is not
This tutorial is a coding tutorial. You'll learn here how to program filters into pages. We'll write some html and php code to achieve this. If you're looking for a tutorial on how to use the filters page to perform a search, please refer to the section working with filters of the online help.Filters work in groups
AppGini supports up to 80 filters grouped into 20 groups. The filters page exposes only 12
filters grouped into 3 groups. But this is still enough to create complex searches. For
example, the filters defined in the screenshot to the right create a sophisticated search
of the orders table in the
Northwind online demo. Here is a quick
preview of the filters page in action
(click the "Apply Filters" button to view the filtered orders.)
So, for the example filters to the right, the generated SQL query would be structured like this:
Filter variables
The filters form in AppGini passes several variables to the application that define filters. If we understand these variables, we'll be able to create default filters, enforce a filter on pages, build customized search forms, ... etc.Each filter has an index that identifies it. The first filter has an index of 1, the second one has an index of 2, and so on. We could define up to 80 filters (but that could possibly slow down our queries). Each filter is composed of 4 variables, as outlined in the table below.
| Variable name in HTML | Possible values | Details | |
|---|---|---|---|
| FilterAnd[i] | and or |
Specifies how this filter is combined with the previous one. For the first filter (i=1), there is no need to specify a value for this variable. | |
| FilterField[i] | 1, 2, 3, .. etc. | Specifies which field in the table is to be filtered. The first field of the table (as defined in your AppGini project) is 1, the second is 2, ... etc. | |
FilterOperator[i] |
equal-to not-equal-to greater-than greater-than-or-equal-to less-than less-than-or-equal-to like not-like is-empty is-not-empty |
Specifies how to treat the FilterValue. For versions of AppGini older than 5.40, please refer to the table below |
|
| FilterValue[i] | Any value we're looking for | ||
| Where i is the index of the concerned filter, a value from 1 to 80. | |||
| FilterOperator | Description |
|---|---|
| <=> | Equal to |
| != | Not equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| like | Like |
| not like | Not like |
| isEmpty | Is empty |
| isNotEmpty | Is not empty |
Implementing filters in the generated code
In many AppGini applications, we'd like users to view only a subset of data rather than viewing all the records of a table. For example, in the orders table, we might want users to view the orders as of 1/1/2009 only. To do so, we'd write the PHP code for filtering the orders table by date as follows./* filter 1 code*/ /* let's assume the date field is the fourth field in the orders table*/ addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009');So, the above code limits the orders table list only orders dating 1/1/2009 or newer. Now, where should this code be placed? It should be placed in the generated "hooks/tablename.php" file inside the tablename_init() function, where tablename is the name of the concerned table. For our example, the file would be named "hooks/orders.php" and the function orders_init(). So, the code in the file would look like this:
function orders_init(&$options, $memberInfo, &$args){
addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009');
return TRUE;
}
Using multiple filters
Let's say we want to filter orders to display only the orders of January 2009. In this case, we'll add another filter to our code to limit orders to the period from 1/1/2009 to 1/31/2009 [1].addFilter(1, 'and', 4, 'greater-than-or-equal-to', '1/1/2009'); addFilter(1, 'and', 4, 'less-than-or-equal-to', '1/31/2009');We've now finished part 1 of this tutorial. We've learnt how to enforce a filter on users when they view a certain table. In the next part of this series, we'll learn how to further customize the enforced filters.
Related pages
Creating a customized search formApplying a default filter on a table
[1] In this tutorial, I'm assuming the date format specified in your AppGini project is "Month, day, year" with a Slash separator. If you're using a different format, you should change the code above to reflect that format.