Recordsets limitations

[astefl]

I noticed that when you use the __sql__ in the fields.ini file xataface caches the recordset limiting any other searches to what the __sql__ is limited to. If I want to have the system show a default of the last 3 days of records, but still be able to search on the whole table, what is the best way to accomplish this?

[shannah]

I know it is tempting to use the __sql__ directive to filter the result set for a table, but this is not a good idea (though it may work). The __sql__ directive was intended to graft on additional fields to the table, but to keep the resultset itself unchanged. Filtering should be done via queries or via security filters.

Filtering records to show only the last 3 days by default can be done in a number of different ways. Since this isn't a security feature, but one of convenience it is best to do this via queries. You can manually add any query that you like by adding the appropriate values to the $_GET and $_REQUEST arrays prior to initializing Xataface (i.e. at the beginning of your index.php file), or by setting the value in the $query in the beforeHandleRequest method of the application delegate class.

Some guidelines for setting these automatic query parameters:

1. You should NOT modify a query on a $_POST request because $_POST requests generally update data in the database and may be relying on the query parameters from the previous request to know what it is updating.

2. Try not to override user query preferences (unless it is your intention to frustrate users). Only override default prefs.


An example

In the application delegate class:

Code: Select all

function beforeHandleRequest(){
    if ( !$_POST ) {   // We check to make sure this isn't a $_POST request
        $query =& Dataface_Application::getInstance()->getQuery();
        if ( $query['-table'] == 'my_table' and !isset($query['date_created']) ){
            // Only show records where date_created field is marked within last 3 days
            $query['date_created'] = '>='.date('Y-m-d H:i:s', strtotime('-3 days'));
        }
    }
}