• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

List Pre-filters

  • Views Views: 46,723
  • Last updated Last updated:

Navigation

  • Pre-filter​


    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:
        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:
        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:
      SELECT a,b,c FROM some-table WHERE foo=bar
      Then {$q->a} can be used as a placeholder in the value field.
    • Getting records from a list where the date needs to be greater than or equal to the current date e.g in the case of upcoming events use pre-filter WHERE Field =id Condition= IN Value =
      Code:
      SELECT id FROM mytable WHERE date >= CURRENT_DATE
      Type = Query.

    Notes for IN and NOT IN:​

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

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

    Adding an additional pre-filter​


    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:

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



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

    | Name | Age | Sex |
    +----------------------------------+
    | John | 19 | Male |
    +----------------------------------+

    Changing all of the ANDs for ORs would give this query:

    Code:
    where name EQUALS John (type text) OR age EQUALS 19 (type no quotes)OR sex EQUALS male

    and this data set:

    Code:

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

    | 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 only the latest record​


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


    Show records belonging to the logged on user​


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

    For showing all records to admins:
    add a second prefilter (OR) with a condition always true (e.g. where id >= 0) and apply to (super)admin

    Pre-filters for Front End Users Only


    Assigning access based on the view level element plug-in.​


    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
Back
Top