SearchPanes

SearchPanes is a powerful tool which is used to search a DataTable through a series of panes which are populated with options from the table. As of SearchPanes 1.1 server-side processing is supported, making SearchPanes useful for those with large data sets. The Editor PHP libraries provide integration with SearchPanes' server-side processing support, as detailed here.

SearchPanes operates by using a list of unique options which are collected from the dataset. This means we need a way to gather that unique list of options from the database. Editor's PHP libraries provide a SearchPaneOptions class for this purpose.

As SearchPanes has the ability to show a pane for each column, each field Field instance provides a Field->searchPaneOptions() method to define the options that will be shown for that column / pane. It is operationally very similar to the Options class for joined tables and can be called with a SearchPaneOptions class instance which defines the table and columns to read from the database.

It is worth noting that custom panes will not be supported by server-side processing in SearchPanes 1.1 but this is something that we are aiming to provide in the future with SearchPanes 1.2.

SearchPaneOptions class

The SearchPaneOptions class provides a simple API to define the information that is required to read the options from the database, as well as customisation options. It is constructed using SearchPaneOptions::inst() (or new SearchPaneOptions() if you are using PHP 5.4+) and provides the following chainable methods:

• SearchPaneOptions->table( string ) - The database table name that the options should be read from
• SearchPaneOptions->value( string ) - The column name that contains the value for the list of options
• SearchPaneOptions->label( string|string[] ) - The column(s) that contain the label for the list of options (i.e. this is what the user sees in the select list)
• SearchPaneOptions->where( function ) - closure function that will apply conditions to the list of options (i.e. a WHERE statement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - the Query class being used, providing access to its methods such as Query->where(), Query->and_where() and Query->or_where() (refer to the PHP API documentation for full details on the Query class and its methods).
• SearchPaneOptions->render( function ) - A formatting function that will be applied to each label read from the database - this is particularly useful if you pass in an array for the third parameter and wish to customise how those fields are shown. They will simply be concatenated with a single space character if no function is provided.
• SearchPaneOptions->order( string ) - Specify an order by clause that will determine the order of the options. If this method is not used the ordering will be automatically performed on the rendered data.
• SearchPaneOptions->leftJoin(string, string, string, string) - Specify a _left join- clause that will allow SearchPanes to read values and labels from different tables.

Consider the following five use cases:

1) Simplest possible use - Letting SearchPaneOptions work out values for table, value and label

Field::inst( 'users.site' )
    ->searchPaneOptions( SearchPaneOptions::inst() );

2) Get all of the details required for SearchPanes from the sites table, where id is the value and name the label:

Field::inst( 'users.site' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( 'name' )
    );

3) Apply a condition to the list of options for SearchPanes - in this case getting only names which start with the letter 'L':

Field::inst( 'users.site' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( 'name' )
        ->where( function ($q) {
            $q->where( 'name', 'L%', 'LIKE' );
        })
    );

4) Get multiple fields for the label and format them using a function. In this case the resulting format is: 'name (test)' - e.g.: 'Edinburgh (test)':

Field::inst( 'users.site' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->render( function ( $str ) {
            return $str.' (test)';
        } )
    );

5) Perform a left join between users and sites to get the desired data:

Field::inst( 'sites.name' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->value( 'users.site')
        ->label( 'sites.name' )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    );