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 .NET 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 .NET 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 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 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.AndWhere() and Query.OrWhere() (refer to the .NET 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

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:

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" )
    );