Click here to Skip to main content
14,355,397 members

Multiple Column Sorting: from Angular NgExTable to Source Data List Management

Rate this:
5.00 (2 votes)
Please Sign up or sign in to vote.
5.00 (2 votes)
28 Sep 2019CPOL
Implementing and describing multiple column sorting features with the Angular data grid tool NgExTable and associated code for sorting the source data list

Introduction

My previous article, Client and Server-Side Data Filtering, Sorting, and Pagination with Angular NgExTable, describes the base structures and uses of the custom data grid tool. This is the second article of the NgExTable series, which details the multiple column sorting practices and workflow with this Angular UI tool, API data services, and the database stored procedure. Audiences who use the Microsoft Visual Studio and C# can download sources and play the code with all scenarios in the front-end, middle-tier data services, and database back-end. Audiences who download the source code for any platform other than using Microsoft technologies can still look into how the multiple column sorting works with the Angular data grid tool and the logic to generate client-side source data list or server-side mock data result sets.

The sample application demonstrates these unique features regarding the data list sorting:

  • Providing different approaches to switch between single and multiple column sorting types
  • Displaying multiple column sorting UI styles only when more than one sorted column is present
  • Sorting sequence for any sortable column can be changed with free and easy selections
  • Switching existing sorting sequence numbers intelligently with the position-down rule (see details in the Sorted Column and Sequence Selections section)
  • Dynamically showing and hiding the multiple sorting command panel for using the on-submit pattern of the data access to avoid repeated and redundant calls for the data, especially for the server-side pagination operations
  • Optional display of row grouping lines based on the first sorted column data
  • Table-hosting component level configurations for different column sorting types and related options so that the settings can be different for the grid tool used on different pages in an application
  • Code examples for source data list processed for multiple column sorting in the client-side JavaScript, API data services in C#, and the SQL Server database stored procedure

Set Up and Run Sample Application

NOTE: This section may repeat most of the setup steps in the previous article for the NgExTable.

The downloaded sources contain different Visual Studio solution/project types. Please pick up those you would like and do the setup on your local machine. You also need the node.js (recommended version 10.16.x LTS or above) and Angular CLI (recommended version 8.1.2 or above) installed globally on the local machine. Please check the node.js and Angular CLI documents for details.

When you play the sample application with the Visual Studio, please check the available versions of the TypeScript for Visual Studio in the C:\Program Files (x86)\Microsoft SDKs\TypeScript folder. Both ASP.NET 5 and Core types of the sample application set the version of TypeScript for Visual Studio to 3.4 in the TypeScriptToolsVersion node of SM.NgExDialog.Sample.csproj file. If you don't have the version 3.4 installed, download the installation package from the Microsoft site.

NgExTable_AspNet5_Cli

  1. Download and unzip the source code file to your local work space.

  2. Go to physical location of your local work space, double click the npm_install.bat and ng_build.bat files sequentially under the SM.NgExTable.Web\ClientApp folder.

    NOTE: The ng build command may need to be executed every time after making any change in the TypeScript/JavaScript code, whereas the execution of npm install is just needed whenever there is any update with the node module packages.

  3. Open the solution with Visual Studio 2017 or 2019 (Visual Studio 2015 with Update 3 or above also works) and rebuild the solution with the Visual Studio.

  4. Click the IIS Express toolbar command (or press F5) to start the sample application.

NgExTable_AspNetCore_Cli

  1. You need to install the .NET Core 2.2 on the local machine.

  2. Download and unzip the source code file to your local work space.

  3. Go to physical location of your local work space, double click the npm_install.bat and ng_build.bat files sequentially under the SM.NgExTable.Web\AppDev folder (also see the same NOTE for setting up the NgExTable_AspNet5_Cli project).

  4. Open the solution with the Visual Studio 2017 (version 15.9 or above), or Visual Studio 2019, and rebuild the solution with the Visual Studio.

  5. Click the IIS Express toolbar command (or press F5) to start the sample application.

Since the data grid loaded with the client-side pagination sets the single column sorting by default, to look at the page using the multiple column sorting, you can click the Server-side Pagination menu item and then click Go button on the search panel.

Image 1

You don't have to set up the server data source from the data-providing service application for the server-side pagination and sorting demo. By default, the sample application uses the server-mock-data.service.ts to simulate the server-side data request and response patterns and results. This also benefits the audiences who add the NgExTable_Scripts_AnyPlatform code files into their website project and run for the NgExTable example on their non-Microsoft platforms.

If you run the sample application with Visual Studio and would like to call the real server data source for the server-side pagination and sorting demo, I recommend performing these steps:

  1. Go to my other article, ASP.NET Core: A Multi-Layer Data Service Application Migrated from ASP.NET Web API and set up the Core API data services following the instructions. The source code files can also be downloaded on the beginning of this article.

  2. Start the Core API data services with the Visual Studio and keep the running solution with the IIS Express on the background.

  3. In the ../app/NgExTableDemo/app.config.ts file, change the ServerPagingDataSource from mock to server.

    export const ServerPagingDataSource: string = 'server'; //'mock' for 
                                                            //simulating server data.
  4. Press F5 to run the NgExTable demo application, select the Server-side Pagination left menu item, and click the Go button on the Search Products panel.

If you would like to use the legacy ASP.NET Web API 2 version of the data services, you can also see the instructions on the data service article for details. You need to enable this line in the ../app/NgExTableDemo/app.config.ts file for accessing the Web API 2 data sources.

export const WebApiRootUrl: string = 'http://localhost:20422/api/'; //Web API 2.0

TIP: You can modify the source data to make the same value for columns to be sorted in several rows so that the multiple column sorting feature is meaningfully displayed.

  • The client data source file locations: 

    • NgExTable_AspNet5_CliSM.NgExTable.Web/ClientApp/local-data-products.json
    • NgExTable_AspNetCore_CliSM.NgExTable.Web/wwwroot/ClientApp/local-data-products.json
    • NgExTable_Scripts_AnyPlateformClientApp/local-data-products.json
  • The server data source seeding file location in the data services:

    • SM.Store.CoreApi_2.2SM.Store.Api.DAL\DataContext\StoreDataInitializer.cs
    • SM.Store.WebApiSM.Store.Api.DAL\StoreDataContextInitializer.cs

    Or you can directly change the data values in the SQL Server database using the SQL Server Management Studio for the server data source. 

UI Structures for Data Sorting

The below diagram illustrates the major components used for UI data sorting processes and their relationships.

Image 2

These structures are added into the table-hosting HTML template based on the data sorting needs.

<table class="table table-condensed table-striped bottom-border"

        [table-main]="pagingParams" (tableChanged)="onChangeTable($event)">
    <thead>
        <tr class="option-board-tr">
            <th colspan="6">
                <options (optionChanged)="onChangeOptions($event)"></options>
            </th>
        </tr>
        <tr #trHead class="table-header-tr">
            <th>Product Name<column-sort sortBy="ProductName" 

                sortDirection="asc" sequence="3"></column-sort></th>
            <th>Category ID<column-sort sortBy="CategoryId"></column-sort></th>
            <th>Category Name<column-sort sortBy="CategoryName"></column-sort></th>
            <th>Unit Price<column-sort sortBy="UnitPrice"></column-sort></th>
            <th>Status<column-sort sortBy="StatusDescription" 

                sortDirection="asc" sequence="2"></column-sort></th>
            <th>Available Since<column-sort sortBy="AvailableSince" 

                sortDirection="desc" sequence="1"></column-sort></th>
        </tr>
        <tr>
            <th colspan="6" class="sort-box-th">
                <multi-sort-command (multiSortChanged)="onChangeTable($event)">
                </multi-sort-command>
            </th>
        </tr>
    </thead>
    - - -
</table>

Here are some key points regarding the sorting-related directive and component structures.

  • TableMainDirective: This attribute directive for the HTML table is a component without the HTML template. It acts as the local service privately for the current table. It can be imported and injected into any child component that is only declared within the current table scope. Thus, in the sample application, those child components, OptionsComponent, ColumnSortingComponent, and MultiSortingCommandComponent, can call the tableMainDirective as a central point for communications.

  • OptionsComponent: It is placed within the HTML table header. The component and its HTML template are pre-defined in the NgExTable library packages so that the code in the client calling side (the NgExTableDemo section of the sample application) would be very simple. The component transfers the data and submits changes via the tableMainDirective.

  • ColumnSortingComponent: It is used to display and handle sorting icons and sequence numbers. Multiple instances of the component can be defined for the sortable columns as shown in the above HTML code. Thus, one-to-many relationship exists between the parent TableMainDirective and child ColumnSortingComponent instances. If executing child code pieces initiated from the parent, then the same code in all child instances will be iterated.

  • MultiSortingCommandComponent: It renders the multiple column sorting command panel for submitting or cancelling the sorting request with the “on-submit” pattern. This component also only communicates with the TableMainDirective through which the input and output data operations are performed. The UI panel is dynamically rendered and displayed based on the required multiple column sorting operations (see details in the later section, Multiple Column Sorting Command Panel).

Sorting Types and Configurations

Although the multiple column sorting feature is available, sometimes a single column sorting of the data option may still be practical in many cases. The sample application can be configured to run either single column sorting type only or combination of single and multiple column sorting types. Those configurations must be in the table-hosting component, not the global, level since multiple data grids with different settings may be used in an application.

Single Column Sorting Only

This is the traditional operation mode for the data grid display. You can use this type if the multiple column sorting is not needed by the design or the multiple column sorting option is not available from the data sources.

To run the application with the single column sorting mode only, set this line in the ngOnInit method of the table-hosting component class:

this.tableMainDirective.sortingRunMode = 0; //0: single column sorting only, 
                                            //1: single/multiple column sorting (default)

In the sample application, the Client-side Pagination demo page loads the data grid with the single-column sorting type only. You can comment out this configuration line or set the value to 1 to enable the switchable single and multiple column sorting types for this page.

Switchable Single and Multiple Column Sorting

With this setting, both single and multiple column sorting types can co-exist for the data grid. But the question is how to easily and intelligently switch between the single and multiple column sorting types. We will discuss this topic in details with two approaches, dropdown selection and Ctrl (or Shift) key operation, in the next section.

To run the application with the switchable, set this line in the ngOnInit method of the table-hosting component class. You can also omit this line since it's the default setting.

this.tableMainDirective.sortingRunMode = 1;

By the default, the sortingTypeSwitch is set to using the dropdown selection. See the details in the next section for using the Ctrl/Shift key approach.

If using the dropdown selection, you may also to set the default loading type for the single or multiple column sorting. Otherwise, the default setting is to load the single column sorting as active selected type.

this.tableMainDirective.sortingOption = 'multiple'; //'single' (default) or 
                                  // 'multiple' - used only for sortingRunMode = 1

In the sample application, all configuration items for column sorting are defined with comments in the TableMainDirective controller class.

//Values here are defaults and can be overwritten from table-hosting component-level settings.    
sortingRunMode: number = 1;     //0: single column sorting only, 
                                //1: single/multiple column sorting
sortingTypeSwitch: number = 0;  //0: dropdown selection mode, 
                                //or 1: Ctrl/Shift key mode - used only for sortingRunMode = 1
sortingOption: string = 'multiple'; //'single' or 'multiple' - initial loading type 
                                    // used only for sortingRunMode = 1
enableOptionBoard: string = ''; //'yes' or 'no'('' the same as 'no').
showOptionBoardContent: string = '';//'yes' or 'no'('' the same as 'no')
showGroupingLines: string = ''; //yes' or 'no'('' the same as 'no')

All configuration items are also listed with comments in the ngOnInit method of the ClientPagingComponent and ServerPagingComponent classes to demonstrate how to use or overwrite the defaults. Feel free to enable, disable, or change the item lines for testing the data sorting results and display after you have known all details of the sample application.

Switching between Single and Multiple Column Sorting Options

The sample application demonstrates two different approaches for switching between single and multiple column sorting types, each with its pros and cons. For your real application development, you can implement one of the approaches based on your preference and business requirements.

Selecting Sort Type from Dropdown List

This approach is configured as using the dropdown selection when the value of TableMainDirective.sortingTypeSwitch value is 1.

sortingTypeSwitch = 1; //dropdown selection,

The OptionsComponent and its template are created for the dropdown list and selection display. In the OptionsComponent, the dropdown selected value is assigned to the TableMainDirective.sortingOption. When the option is changed, the switchSortingOption method is then called to process the changes in sorting logic and data results. Since the TableMainDirective is injected into the contract of the OptionsComponent class, the properties and methods in the TableMainDirective can directly be accessed in the OptionsComponent.

onSortingOptionChange($event: any) {
    this.tableMainDirective.sortingOption = this.sortingOption;        
    this.tableMainDirective.switchSortingOption();
}  

The option panel and column headers, when loaded with multiple column sorting by default, look like the below:

Image 3

When changing to the single column sorting, the process automatically takes the first sorted column from the previous multiple column sorting.

Image 4

With the explicit selection, the user can clearly know the current sorting type and the consequences of conducted actions. However, this approach needs an additional component and space for the dropdown display on the page.

Clicking Sorting Icons with Ctrl or Shift Key

This approach or feature is not loaded by default in the sample application. You need to enable it by setting the value of TableMainDirective.sortingTypeSwitch value to 0 in the ngOnInit method of the table-hosting component controller class.

sortingTypeSwitch = 0; //Using Ctrl or Shift key,

With this approach, regularly clicking any sorting icon on any sortable column always proceeds with the single column sorting type. When clicking any sorting icon on the second sortable column while pressing the Ctrl or Shift key, the process will go to the multiple column sorting workflow. The toggelSort method in the ColumnSortingComponent checks such user actions.

toggleSort(obj: any, $event: any) {

    if (this.config.sortingTypeSwitch == 0 && ($event.ctrlKey || $event.shiftKey) &&
        !this.tableMainDirective.baseSequenceOptions.find(a => a.value > 1)) {
        if (this.config.sortingOption == 'single') {
            //Switch to multiple mode.
            this.tableMainDirective.sortingOption = 'multiple';
        }
    }
    - - -
}

At this point, any user action will follow the multiple column sorting scenarios, the same as those used with selecting the multiple column sorting from the dropdown list. No explicit structure for any selection is needed though. The downsides of the approach are also obvious.

  • Users may not clearly know the operation with the Ctrl or Shift key so that it's better to provide additional instructions.
  • For switching back to the single column sorting type, it's weird if still using the Ctrl or Shift key. Thus, some other ways need to be considered.

You can remove the sorted columns by clicking the x button from the sequence dropdowns until only one sorted column is left on the data grid, which then becomes the single column sorting type. This, however, is certainly a cumbersome way for the purpose. To quickly switch to the single column sorting type, a button, Go to Single Column Sorting, is added onto the multi-sorting command panel. The details of the panel will be described in later section but here just shows the panel with the button which is rendered on the panel only for Ctrl or Shift key activation pattern. Clicking the button will switch immediately to the single column sorting type, leaving the previously first sorted column there. You can also notice that no sorting type dropdown is displayed.

Image 5

Column and Sequence Selections

The multiple column sorting feature is implemented with complex processing logic for selections of sorted columns and sequences. Audiences may not have to know the internal code details when using the feature if no modification for the logic is needed, but below are outlined the essential points of implementations and practices.

  1. The ColumnSortingComponent object instance is created one for each sortable column. The instances set the sorting icons and sequence display with the dropdown list on the column headers.

  2. The parent TableMainDirective object governs multiple instances of the ColumnSortingComponent by the data members sortableList and baseSequenceOptions arrays. For example, the sortableItem is created in the ColumnSortingComponent and then added into the sortableList array in the TableMainDirective.

    In the ColumnSortingComponent:

    //Populate sortableItem and add sortable column to sortableList in parent.
    this.sortableItem = {
        sortBy: this.sortBy,
        sortDirection: this.sortDirection || '',
        sequence: this.sequence || -1
    };
    
    this.tableMainDirective.initSortableList(this.sortableItem);    

    In the TableMainDirective:

    //Called from each columnSortComponent.
    initSortableList(sortableItem: SortableItem) {
        - - -
        this.sortableList.push(sortableItem);
        - - -  
    }
  3. The toggleSort method in the ColumnSortingComponent handles the changes in sorted column selections and updates the sorting direction for the corresponding sortableItem.

    toggleSort() {
        - - -                
        switch (this.sortableItem.sortDirection) {
            case "asc":
                this.sortableItem.sortDirection = "desc";
                break;
            case "desc":
                this.sortableItem.sortDirection = 
                      this.toggleWithOriginalDataOrder ? "" : "asc";
                break;
            default:
                //Existing sortDirection is ''.
                this.sortableItem.sortDirection = "asc";
                break;
        }
        - - -
    }
  4. When the sorting sequence changes, the code in the onSequenceChange method of the ColumnSortingComponent needs to call the parent TableMainDirective for re-arranging the sequence values and refreshing overall sorting settings since any change in a column will affect behaviors of other columns.

    onSequenceChange(event: any) {
        let oldSeq: number = this.sortableItem.sequence;
        this.sortableItem.sequence = event.value;
                           
        //Call to re-arrange sequence numbers. 
        this.tableMainDirective.rearrangeSequence
                (oldSeq, this.sortableItem.sequence, this.sortBy);                
                
        //Call TableMainDirective and then back call each ColumnSortComponent 
        //to reset sorting settings if changed.
        this.tableMainDirective.refreshSortSettings();
        - - -        
    }
  5. The sorting sequence arrangements are based on these rules:

    • The next available sequence number is automatically assigned to any newly added column for the sorting.

    • The maximum available sequence number is dynamically updated with only the number of active sorted columns. For example, if three sorted columns are selected, then the sequence dropdown list only contains 1, 2, and 3.

      Image 6

    • Changing any column sequence by selecting the number from the dropdown list will automatically re-arrange the sequence numbers towards the larger number side. For example, if changing the sequence number from 4 to 2, the previous 2 will be 3, and previous 3 be 4. The rearrangeSequence method in the TableMainDirective implements the desired rules and logic.

      rearrangeSequence(oldSeq: number, newSeq: number, sortBy: string) {
          if (oldSeq > 0 && newSeq == -1) {
              //Re-arrange sequence if any sortableItem.sequence reset to -1.
              this.sortableList.forEach((item: SortableItem, index: number) => {
                  if (item.sequence > oldSeq) {
                      item.sequence--;
                  }
              });
          }
          else {
              //Change any sequence number in positive range.
              this.sortableList.forEach((item: SortableItem, index: number) => {
                  if (item.sortBy != sortBy) {
                      if (item.sequence >= newSeq && item.sequence < oldSeq) {
                          item.sequence++;
                      }
                      else if (item.sequence <= newSeq && item.sequence > oldSeq) {
                          item.sequence--;
                      }
                  }
              });            
          }	
  6. In the TableMainDirective, any escalated changes in sorted column selections and/or sequences will be transferred to the pagingParams.sortList array that can be sent to the table-hosting component for refreshing the data grid. If current sorting option is single column type, the pagingParams.sortList is populated with only the first sortableItem from the sortableList.

    updatePagingParamsWithSortSettings() {
        //Transfer active sortable items to pagingParams.sortList.
        this.pagingParams.sortList = [];
        for (let num: number = 1; num <= this.sortableList.length; num++) {
            for (let idx: number = 0; idx < this.sortableList.length; idx++) {
                if (this.sortableList[idx].sequence == num) {
                    let sortItem: SortItem = {
                        sortBy: this.sortableList[idx].sortBy,
                        sortDirection: this.sortableList[idx].sortDirection
                    };
                    this.pagingParams.sortList.push(sortItem);
                    break;
                }
            }
            if (this.sortingOption == 'single' && num == 1) {
                break;
            }
        }
    }

Multiple Column Sorting Command Panel

For the multiple column sorting, most Angular data grid tools that could be found across the Internet and software market use the "on-change" data access pattern in which any change action on the sorted column selection or sequencing would reload the data items to the grid, no matter if it's an intermediate step. As a result, any designated sorting operation, if consisting of multiple steps, would call for the data and refresh the grid multiple times.

To resolve the issue, the NgExTable introduces the MultiSortingCommandComponent and its HTML template with the "on-submit" data access pattern. The panel can dynamically be expended and collapsed directly under the table header.

  1. Add the <multi-sort-command> tag into the second row of the <thead> in the table-hosting component template.

    <thead>
        <tr #trHead>
            <!--Normal th headers -->
            - - -
        </tr>
        <tr>
            <th colspan="6" class="sort-box-th">
                <multi-sort-command (multiSortChanged)="onChangeTable($event)">
                </multi-sort-command>
            </th>
        </tr>
    </thead>
  2. The command panel would automatically be shown when any set of below conditions is met.

    Condition set #1:

    • The current sorting option is in the multiple column type mode with at least one existing sorted column.
    • Clicking the sorting icon on any other column to turn on sorting or change the sorting direction.

    Condition set #2:

    • There are already at least two sorted columns.
    • Clicking any sequence dropdown and having made a sequence number change.

    Below shows the command panel opened with the dropdown selection option switching type.

    Image 7

  3. The user can comfortably and repeatedly make any desired change in column and sequence selections when the command panel is open. The sorting request will only be submitted if clicking the OK button. Alternatively, the user can cancel the changes or clear all sorting settings for getting a non-sorted data set.

  4. The panel will automatically be closed whenever clicking the OK, Cancel, or Clear Column Settings button, or any action outside the panel if the panel is open. The command panel is not the modal type so that any action outside the panel will be treated as an operation of cancellation.

  5. Showing and hiding the panel is controlled by the value of the showMultiSortPanel variable in the MultiSortingCommandComponent. Requests of getting and setting the variable values are sent from other components through the subscription approaches.

    • Setting the showMultiSortPanel variable to false directly in the MultiSortingCommandComponent when clicking any button on the command panel. The panel will then be closed with this action after the corresponding operation is done.

    • Since most top-level processes of multiple column sorting are performed in the TableMainDirective, and calls from this parent to the child component is needed, the methods for getting and setting the showMultiSortPanel value are added into the TableMainDirective. These methods can be called from multiple places of the TableMainDirective itself and the ColumnSortingComponents.

      getShowMultiSortPanelFlag(): boolean {
          let subjectParam: NameValueItem = {
              name: 'getShowMultiSortPanelFlag',
              value: undefined
          }
          this.multiSortCommandComponent.next(subjectParam);
          return subjectParam.value;
      }
      
      setShowMultiSortPanelFlag(flagValue: boolean) {
          let subjectParam: NameValueItem = {u
              name: 'setShowMultiSortPanelFlag',
              value: flagValue
          }
          this.multiSortCommandComponent.next(subjectParam);
      }
    • The method calls are subscribed in the constructor of the MultiSortingCommandComponent. When a calls comes from the parent TableMainDirective, it actually gets and sets the showMultiSortPanel variable value in the MultiSortingCommandComponent.

      let pThis: any = this;
      //Called from TableMainDirective to open this panel.
      this.tableMainDirective.multiSortCommandComponent$.subscribe(
          (subjectParam: NameValueItem) => {
              if (subjectParam.name == "setShowMultiSortPanelFlag") {
                  //subjectParam.value: true or false.
                  pThis.showMultiSortPanel = subjectParam.value;
              }
              else if (subjectParam.name == "getShowMultiSortPanelFlag") {                    
                  subjectParam.value = pThis.showMultiSortPanel;
              }
          }
      );

Default Settings for Sorting Parameters

With the NgExTable, the default values of sorting parameters can be specified in one of either two places.

  1. Add the sortDirection and sequence attributes and values into the column-sort tags in the table-hosting HTML template.

    <tr>
        <th>Product Name<column-sort sortBy="ProductName" 
    
            sortDirection="asc" sequence="3"></column-sort></th>
        <th>Category ID<column-sort sortBy="CategoryId"></column-sort></th>
        <th>Category Name<column-sort sortBy="CategoryName"></column-sort></th>
        <th>Unit Price<column-sort sortBy="UnitPrice"></column-sort></th>
        <th>Status<column-sort sortBy="StatusDescription" 
    
            sortDirection="asc" sequence="2"></column-sort></th>
        <th>Available Since<column-sort sortBy="AvailableSince" 
    
            sortDirection="desc" sequence="1"></column-sort></th>
    </tr>
  2. Add elements into the pagingParams.sortList array in the ngOnInit() of the table-hosting component. The element orders determine the column sorting sequences.

    this.pagingParams = {
        pageSize: pageSize !== undefined ? pageSize : 10,
        pageNumber: 1,
        sortList: [{
            sortBy: 'AvailableSince',
            sortDirection: 'desc'
        }, {
            sortBy: 'StatusDescription',
            sortDirection: 'asc'
        }, {
            sortBy: 'ProductName',
            sortDirection: 'asc'
        }],
        changeType: TableChange.init
    }

If no default item and value exist, the grid will initially be populated without any column sorting of the data.

Display Enhancement with Sorted Data

Many display options for the sorted data list can be added into the page by changing the styles of the nativeElement items in the AfterViewInit event handler or after the data loading. The sample application shows an example of adding row grouping lines into the grid display based on the first sorted column. The main method, setRowGroupLines, is created in the TableMainDirective to dynamically process the DOM based style settings. Audiences can look into the code details in the table-main.directive.ts file.

Such style settings are done in the library, rather than in the UI, which is just an example to save the code in the UI parts. Passing the DOM elements from the table-hosting component to the TableMainDirective is still needed. Thus, two ViewChild objects are defined in the table-hosting component, where the trHead and trItems are created for template variables, #trHead and #trItems, respectively.

//Row group lines.
@ViewChild('trHead', { static: true }) trHead: ElementRef;
@ViewChildren('trItems') trItems: QueryList<any>;

After the data loading, the code then calls the setRowGroupLines method by passing two ViewChild object instances.

//Show first sortBy group lines in grid. 
this.tableMainDirective.setRowGroupLines(this.trHead, this.trItems);

The showGroupingLines option can also be enable and disabled using the checkbox on the option switchboard. You may want to turn off the grouping lines if rows sorted by the column are pretty unique, in which the grouping lines may not make much more sense.

The below screenshot shows the row grouping lines in action with the AvailableSince as the first sorted column.

Image 8

Various other display styles can also be enhanced using the described approach above, such as changes in row background color, text font type and color, merging cells, or even adding sub-rows. Feel free to experiment by yourselves.

Sorting Source Data for Client-side Pagination

The TypeScript code in the NgExTable/client-pagination.service.ts performs the sorting logic for the client-side pagination data list. In the sample application, the same native JavaScript array.sort function and associated processes are also used for the server-mock data provider.

//Sorting logic.
changeSort(pagingParams: PagingParams, data: Array<any>): Array<any> {        
    let pThis: any = this;
    let rtnArr: any = data.sort((previous: any, current: any) => {
        //Sort firstly-available column with different comparison items along the sortList.
        let idx: number = 0; 
        while (idx < pagingParams.sortList.length) {
            if (current[pagingParams.sortList[idx].sortBy] 
                     !== previous[pagingParams.sortList[idx].sortBy]) {
                return pThis.doSort(previous, current, pagingParams.sortList, idx);
            }
            idx++;
        }            
        return 0;            
    });        
    return rtnArr;
}        

private doSort(previous: any, current: any, 
               sortList: Array<SortItem>, idx: number): number {        
    //Null is sorted to the last for both asc and desc.
    if (previous[sortList[idx].sortBy] === null) {
        return 1;
    }
    else if (current[sortList[idx].sortBy] === null) {
        return -1;
    }
    else if (previous[sortList[idx].sortBy] > current[sortList[idx].sortBy]) {
        return sortList[idx].sortDirection === 'desc' ? -1 : 1;
    }
    else if (previous[sortList[idx].sortBy] < current[sortList[idx].sortBy]) {
        return sortList[idx].sortDirection === 'asc' ? -1 : 1;
    }
    return 0;
}

Here are the important points of the code:

  • The code handles both single and multiple column sorting scenarios.

  • Within the data.sort method that is actually an outer loop, the inner while loop is used for searching the sortable columns with the sequence in the sortList array.

  • The doSort comparing function called from the while loop conducts the real data sorting for each iterated column group.

  • The logic sorts the null value to the last, no matter what sorting direction is. If you apply different rules for the null value, you may change the return index numbers in the doSort function, for example, switching between -1 and 1 in the first if and else if conditions to have null value always sorted the first.

Sorting Source Data for Server-side Pagination

The sample application presents the examples of the server-side data sorting management with both the LINQ Expression and the database stored procedure. These are implemented with the Microsoft technologies so that the audiences need to open the source code projects using the Visual Studio tool to see the demo results. Setting up the Visual Studio API data service projects locally is detailed in the Set Up and Run Sample Application section. Or, you can directly see the instructions in the article ASP.NET Core: A Multi-Layer Data Service Application Migrated from ASP.NET Web API.

LINQ Expression

The LINQ Expression approach is a choice for some simple requests without complex business rules, many table joins, or multiple database instances. The essence of this approach is to construct the LINQ Expression tree nodes with the passed sortList collection as the single or multiple column sorting parameter. The logic is processed by the AsSortedQueryable method in the SM.Store.CoreApi_2.2\SM.Store.Api.Common\Classes\GenericMultiSorterPager.cs. The comment lines explain the operations performed by the code.

public static IOrderedQueryable<t> AsSortedQueryable<t>
       (this IQueryable<t> source, List<sortitem> sortList)
{
    if (sortList.Count == 0)
    {
        return null;
    }
    //Create parameter expression node.
    var param = Expression.Parameter(typeof(T), string.Empty);
            
    //Create member expression for accessing properties for first sorted column.
    var property = Expression.PropertyOrField(param, sortList[0].SortBy);

    //Create lambda expression as delegation type for first sorted column.
    var sort = Expression.Lambda(property, param);

    //Call to create sorting expression for the first sorted column.
    MethodCallExpression orderByCall = Expression.Call(
        typeof(Queryable),
        "OrderBy" + (sortList[0].SortDirection == "desc" ? "Descending" : string.Empty),
        new[] { typeof(T), property.Type },
        source.Expression,
        Expression.Quote(sort));

    //Call to create multiple column sorting expressions if more than one sorted column passed.
    if (sortList.Count > 1)
    {
        for (int idx = 1; idx < sortList.Count; idx++)
        {
            var item = sortList[idx].SortBy;

            //The same logic used as for the first column sorting.
            param = Expression.Parameter(typeof(T), string.Empty);
            property = Expression.PropertyOrField(param, item);
            sort = Expression.Lambda(property, param);

            //Use "ThenBy" for more than one column sorting. 
            orderByCall = Expression.Call(
                typeof(Queryable),
                "ThenBy" + (sortList[idx].SortDirection == "desc" ? 
                "Descending" : string.Empty),
                new[] { typeof(T), property.Type },
                orderByCall,
                Expression.Quote(sort));
        }
    }
    //Generate and return LINQ of IOrderedQueryable type.
    return (IOrderedQueryable<t>)source.Provider.CreateQuery<t>(orderByCall);
}

If you would like to test the LINQ Expression approach, enable the line in the NgExTableDemo\services\app.config.ts of the Angular sample application. You may adjust the API data service URL if it’s different from the downloaded original.

export const ServerPagingDataSource: string = 'server';

export const WebApiRootUrl: string = 'http://localhost:5112/api/';  //Core 2.0 - 2.2

Database Stored Procedure

Using a stored procedure is the preferred approach for retrieving the paginated, filtered, and sorted data list, especially when multiple column sorting, multiple instances of databases, or complex business rule processing are needed. The ASP.NET Core API data service sample application contains the code to create the stored procedure, GetPagedProductList, in the SM.Store.CoreApi_2.2\SM.Store.Api.DAL\DataContext\StoreDataInitializer.cs file. When running the data service application, the stored procedure can automatically be added into the SQL Server database specified in the appsettings.json configuration file. The stored procedure is also available if using the in-memory database setting.

Doing multiple column sorting code with the stored procedure is rather simple and straightforward.

  • From the Angular sample application code, include the sortList in the paginationRequest object for the AJAX call (see details in the ../SM.NgExTable.Web/ClientApp/app/NgExTableDemo/server-paging.component.ts).

    //Parameters for data access
    getProductListRequest(): any {
        let req: any = {
            - - -
            paginationRequest: {
                sortList: []
            },
        };
        - - -
        if (this.pagingParams.sortList.length > 0) {
            req.paginationRequest.sortList = this.pagingParams.sortList;
        }
        return req;
    };
  • The data service controller method, Post_GetPagedProductListSp, converts the sortList generic List to the sortString with the format for the SQL ORDER BY clause.

    //Multiple column sorting
    if (request.PaginationRequest.SortList != null && 
        request.PaginationRequest.SortList.Count > 0)
    {
        foreach (var item in request.PaginationRequest.SortList)
        {
            if (sortString != " ") sortString += ", ";
            sortString += item.SortBy + " " + item.SortDirection;
        }
    }	
  • The sortString is passed to the GetPagedProductList stored procedure and embedded in the dynamic query there.

    IF @SortString != ''
    SET @Query = @Query + ' ORDER BY ' + @SortString	

If interested, audiences can also look into the entire script of the stored procedure in the included file, StoreCF8.sql, downloaded from the ApiDataServices.zip.

Summary

The multiple column sorting feature is a nice add-on to a web application with the data grid display. This article and sample application can be helpful resources for how to implement such a feature in both client UI and server processing sides.

History

  • 9/28/2019: Original post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shenwei Liu
United States United States
Shenwei is a software developer and architect, and has been working on business applications using Microsoft and Oracle technologies since 1996. He obtained Microsoft Certified Systems Engineer (MCSE) in 1998 and Microsoft Certified Solution Developer (MCSD) in 1999. He has experience in ASP.NET, C#, Visual Basic, Windows and Web Services, Silverlight, WPF, JavaScript/AJAX, HTML, SQL Server, and Oracle.

Comments and Discussions

 
-- There are no messages in this forum --
Article
Posted 28 Sep 2019

Stats

1.8K views
85 downloads
2 bookmarked