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 NodeJS 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 Node JS 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 provides a simple API to define the information that Editor requires to read the options from the database, as well as customisation options. It is constructed using
new SearchPaneOptions() 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
SearchPaneOptions.where( function )- closure function that will apply conditions to the list of options (i.e. a
WHEREstatement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - the
Queryclass being used, providing access to its methods such as
Query.OrWhere()(refer to the Node JS API documentation for full details on the
Queryclass 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
```\ new Field( 'users.site' ) .searchPaneOptions( new SearchPaneOptions() );
2) Get all of the details required for SearchPanes from the `sites` table, where `id` is the value and `name` the label: ```js new Field( 'users.site' ) .searchPaneOptions( new SearchPaneOptions() .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':
new Field( 'users.site' ) .searchPaneOptions( new SearchPaneOptions() .table( 'sites' ) .value( 'id' ) .label( 'name' ) .where(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)':
new Field( 'users.site' ) .searchPaneOptions( new SearchPaneOptions() .table( 'sites' ) .value( 'id' ) .render( str => str +' (test)') );
5) Perform a left join between users and sites to get the desired data:
new Field( 'sites.name' ) .searchPaneOptions( new SearchPaneOptions() .value( 'users.site') .label( 'sites.name' ) .leftJoin( 'sites', 'sites.id', '=', 'users.site' ) );