Pre-filters

Dec 17, 2016
Pre-filters

  • Pre-filter (top)


    Pre-filters allow you to filter the data that is shown in the list.

    Unlike element filters there is no user interface to interact with pre-filters

    Pre-filters will filter the list's data before it is displayed. Unlike normal filters these are not editable by the end user. This can be used to great effect to filter the list's data, based on the logged-in user's user group. So registered users see a small subsection of the records and administrators see all records.

    Pre-filters can only be applied once the list has been saved. You can add different pre-filters to lists and to a list menu item. If you have set a pre-filter on a list menu item this will take priority, in other words the pre-filters on your list will not show.

    To start working with pre-filters you need to be editing an existing list. If you are creating a new list, fill in the other form fields up to this point and press 'apply' to save and continue editing the form.

    list-data-prefilter.png
    To add your first pre-filter press the "add" button.

    • Field - The element to filter on
    • Condition - What matching should be done to the element's value
    • Value - The value to match on. You can use some standard placeholders, like {$my->id}. You can also use query string replacement, by using a query string arg name in braces, like {foo_id}.
    • Note: If the pre-filter 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 - fabrik will do a textual comparison between the fields data and the value, ie. if the value is John (without quotes) then the pre-filter 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 pre-filter as in the first example if 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 pre-filter as in the first example if the type is set to Query and our value is: select 'John'
      • No quotes - Use this if you want to compare numeric or date values.
    • Apply to - This access control tells Fabrik to ONLY apply the pre-filter IF a group the user belongs to has the selected access level. So setting it to "Public" will mean that the filter is applied to all users including administrators.To give administrators the right to see all records you can add an additonal pre-filter with OR, apply to "Special", setting a condition which is always true (e.g. id GREATER THAN 0).
    • Grouped- The grouped setting 'groups' the current pre-filter to the previous one. So say you have 3 pre-filter statements:
      • where element = 1
      • AND element2 = 2
      • OR element3 = 3

        This sets the pre-filter logic to:

        Code (Text):
        where element = 1 AND element2 = 2 OR element3 = 3
        However, If you grouped the last statement (OR element3 = 3) then your pre-filter logic becomes:

        Code (Text):
        where element = 1 AND (element2 = 2 OR element3 = 3)
    • Pre-filter query - Optional - 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
      Code (Text):
      SELECT a,b,c FROM some-table WHERE foo=bar
      Then {$q->a} can be used as a placeholder in the value field.

    Notes for IN and NOT IN:(top)

    For IN and NOT IN conditions. the values field should supply a comma separated list of values with or without quotes. E.g.
    Code (Text):

    '5',1,2,'three',"foo"
     

    Adding an additional pre-filter (top)


    When you add an additional pre-filter - there are a couple of additional options that need to be selected.

    To explain these settings lets take the example of this data set:

    Code (Text):

    | Name      |  Age      | Sex    |
    +----------------------------------+
    | John      |  19      | Male    |
    | John      |  21      | Male    |
    | Alice      |  19      | Female  |
    | Rob        |  31      | Male    |
    +----------------------------------+
     


    and the following three pre-filters:
    Code (Text):
    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:

    Code (Text):

    | Name      |  Age      | Sex    |
    +----------------------------------+
    | John      |  19      | Male    |
    +----------------------------------+
     
    Changing all of the ANDs for ORs would give this query:

    Code (Text):
    where name EQUALS John (type text) OR age EQUALS 19 (type no quotes)OR sex EQUALS male
    and this data set:

    Code (Text):

    | 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:

    Code (Text):
    where (name EQUALS John (type text) AND age EQUALS 19 (type no quotes)) OR sex EQUALS male
    which in turn gives the following data:

    Code (Text):

    | Name      |  Age      | Sex    |
    +----------------------------------+
    | John      |  19      | Male    |
    | John      |  21      | Male    |
    | Rob        |  31      | Male    |
    +----------------------------------+
     

    Common Examples: (top)


    Show all records older than a certain date: (top)


    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: (top)


    where:
    field = your-date-element
    condition: greater than
    value: NOW()- INTERVAL 6 HOUR
    type=no quotes

    Show only the latest record (top)


    where
    Field: your-date-element
    Condition: EQUALS
    Value: SELECT MAX(your-date-element)
    Type: query


    Show records belonging to the logged on user (top)


    where:
    field = your-user-element(raw)
    condition: equals
    value: {$my->id}
    type=text

    Pre-filters for Front End Users Only


    Assigning access based on the view level element plug-in. (top)


    This is only possible in Fabrik 3.1 (since 07/04/2013)

    where:
    field = a view level element
    condition: IN
    value: {authorisedViewLevels}
    type = no quotes
  • Loading...