__sql__ Delegate Method

return to Delegate class methods

Synopsis

The __sql__ delegate class method can be defined in any delegate class to specify the SQL query that should be used to fetch records for a given table. This method overrides the __sql__? directive of the fields.ini file. This strategy is primarily used to graft columns from another table onto the base table.

Use Caution

This is an advanced feature and, if used incorrectly, can muck up your application. Make sure that your SQL query includes a superset of the columns in the base table, and is a row-for-row match for the rows in the base table. I.e. you should never use an internal join. Always use a left join so that all of the rows of the base table are returned even if the join table doesn't have a corresponding row.

If you want to simply filter a table's records, and don't need to graft any additional columns onto the table, you should use the setSecurityFilters method.

Example

Given the table foo, its delegate class:

class tables_foo {
    function __sql__(){
        return "select f.*, c.category_name from foo f left join categories c on f.category_id=c.category_id";
    }
}

This effectively grafts a column "category_name" onto the foo table based on a join with the categories table.