Pre-filters
From Fabrik
Prefilters allow you to filter the data that is shown in the table.
Unlike element filters there is no user interface to interact with prefilters
Prefilters will filter the table's data before it is displayed. Unlike normal filters these are not editable by the end user. The can be used to great effect to filter the table's data base on the logged in user's user group. So registered users see a small subsection of the records and administrators see all records.
Prefilters can only be applied once the table has been saved.
To start working with pre-filters you need to be editing an existing table. If you are creating a new table, fill in the other form fields up to this point and press 'apply' to save and continue editing the form.
To add your first pre-filter press the "add" button.
- Prefilter query - this can contain an SQL query that is run to get a record from the database. Its data is stored in an object that can be accessible in the pre-filters value field with the variable name $q
- Field - The element to filter on
- Condition - what matching should be done to the element's value
- Value - the value to match on.
- Note: If the prefilter type is set to "eval" then any comments starting with '//' will be stripped from the code
- Type - How Fabrik should interpret the data in the value field:
- Text - fabrk will do a textual comparison between the fields data and the value
ie. If the value is John then the prefilter expression would be where name EQUALS 'John'
- Eval - fabrik expects the value to be a piece of PHP code that returns the value to search on. So, we can make the same prefilter as in the first example if the the type is set to eval and our value is:
return 'John';
- Query - Fabrik expects the value to be an SQL query that returns the value to search on. So, we can make the same prefilter as in the first example if the type is set to Query and our value is:
select 'John';
- 'No quote's - Use this if you want to compare numeric or date values.
- Apply filter beneath - This access control tells FAbrik to ONLY apply the pre-filter IF the user is of the selected access level or Beneath. So setting it to "Administrator" will mean that the filter is applied to all users EXCEPT super administrators.
Contents |
Adding an additional prefilter
When you add an additional prefilter - there are a couple of additional options that need to be selected.
To explain these settings lets take the example of this data set:
| Name | Age | Sex |
| John | 19 | Male |
| John | 21 | Male |
| Alice | 19 | Female |
| Rob | 31 | Male |
and the following three prefilters:
where name EQUALS John (type text) AND age EQUALS 19 (type no quotes)AND sex EQUALS male
The 'AND's here are set up by the drop down list that appears underneath the grouped radio buttons.
This will return the data set:
| Name | Age | Sex |
| John | 19 | Male |
changing all of the ANDs for ORs would give this query:
where name EQUALS John (type text) OR age EQUALS 19 (type no quotes)OR sex EQUALS male
and this data set:
| Name | Age | Sex |
| John | 19 | Male |
| John | 21 | Male |
| Alice | 19 | Female |
Applying grouped to the second query and reverting the second query from OR to AND would give us:
where (name EQUALS John (type text) AND age EQUALS 19 (type no quotes)) OR sex EQUALS male
which in turn gives the following data:
| Name | Age | Sex |
| John | 19 | Male |
| John | 21 | Male |
| Rob | 31 | Male |
Common Examples:
Show all records older than a certain date:
where:
field = {your date element}
condition: less than
value: NOW()- INTERVAL 1 WEEK
type=no quotes
Show all records created in the last 6 hours:
where:
field = {your date element}
condition: greater than
value: NOW()- INTERVAL 6 HOUR
type=no quotes
Show records belonging to the logged on user
where:
field = {your user element}
condition: equals
value: {$my->id}
type=text
