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 fromSearchPaneOptions->value( string )
- The column name that contains the value for the list of optionsSearchPaneOptions->label( string|string[] )
- The column(s) that contain the label for the list of options (i.e. this is what the user sees in theselect
list)SearchPaneOptions->where( function )
- closure function that will apply conditions to the list of options (i.e. aWHERE
statement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - theQuery
class being used, providing access to its methods such asQuery->where()
,Query->and_where()
andQuery->or_where()
(refer to the PHP API documentation for full details on theQuery
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
12Field::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:
123456Field::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':
123456789Field::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)':
12345678Field::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:
123456Field::inst(
'sites.name'
)
->searchPaneOptions( SearchPaneOptions::inst()
->value(
'users.site'
)
->label(
'sites.name'
)
->leftJoin(
'sites'
,
'sites.id'
,
'='
,
'users.site'
)
);
Post new comment
Contributions in the form of tips, code snippets and suggestions for the above material are very welcome. To post a comment, please use the form below. Text is formatted by Markdown.
To post comments, please sign in to your DataTables account, or register:
Any questions posted here will be deleted without being published.
Please post questions in the Forums. Comments are moderated.