lwcでソート及び検索機能を持つテーブル作成

この記事ではlwcで作成する標準な一覧表示するテーブルに昇順降順する機能に加え検索欄に入力内容を表示内容から絞り込みする機能を加えるコードのサンプルを記載するものとなります。詳しい解説は後ほど加えます。
## 1.コードサンプル

```tanakoDtable.html
<template>
    <lightning-card   
        icon-name="custom:custom66"
    >
        <h1 slot="title" style="font-size:14px">店子管理（{recCount}/{totalCount}）</h1>
        <div slot="actions">
                <div class="slds-form_horizontal slds-form slds-grid slds-wrap">
                    <lightning-input type="search" 
                    label="" 
                    placeholder="フリーワード" 
                    value={keyword} 
                    onchange={handleSearchMem} 
                    onkeydown={handleSearchDB}>
                </lightning-input>
            </div>
            <div style="font-size:12px">
                検索可能項目：シーケンス番号/店舗名
            </div>
            </div>
        <div class="slds-scrollable_y" style="height:10rem">
            <template if:true={results}>
                <lightning-datatable
                    key-field="Sequence_Number__c"
                    data={data}
                    columns={columns}
                    sorted-by={sortedBy}
                    sorted-direction={sortedDirection}
                    onsort={sortColumns}
                    show-row-number-column
                >
                </lightning-datatable>
            </template>
        </div>
    </lightning-card>
</template>
```

```tanakoDtable.js
import { LightningElement,api, wire } from 'lwc';
import { ShowToastEvent } from 'lightning/platformShowToastEvent';
import commonSelectRecords from '@salesforce/apex/CommonDmlUtil.commonSelectRecords';

const COLS = [
    { label: 'シーケンス番号', fieldName: 'LinkValue',type: 'url',
    typeAttributes: {label: { fieldName: 'Sequence_Number__c' }},sortable: true},
    { label: 'GW店舗番号', fieldName: 'GW_Store_Number__c'},
    { label: '店舗名', fieldName: 'STORE_NAME__c'},
    { label: '共通ID', fieldName: 'CommonId__c'},
    { label: 'VM審査結果', fieldName: 'VM_Examination_Result__c'},
    { label: '会社名', fieldName: 'Company_Name__c' },    
    { label: 'ショップコード', fieldName: 'Shop_Code__c' }  
];

export default class TanakoDtable extends LightningElement {
    columns = COLS;
    @api recordId; // parent id
    sortedDirection = 'asc';
    sortedBy = 'Name';
    results;
    data;
    recCount = 0;
    totalCount=0;
    keyword = '';
    sort='';

    connectedCallback(){
        //exec
        
        if(this.sortedDirection==='asc'){
            this.sort='ASC';
        }else{
            this.sort='DESC'
        }
        var sql = 'Select '
            + ' Id,Sequence_Number__c,GW_Store_Number__c,'
            + ' STORE_NAME__c,'
            + ' CommonId__c,'
            + ' VM_Examination_Result__c,'
            + ' Company_Name__c'
            + ' From TanakoManagement__c'
            + " Where Contact__r.AccountId = '" +this.recordId +"'"
            + " order by Sequence_Number__c "+this.sort+" limit 5";
            console.log('test 1 sql>' + sql);
            commonSelectRecords({strSql: sql })
            .then(result => {
            //console.log('test 1 result>' + result);
            //this.results = result;
                
            // var a  = JSON.parse(JSON.stringify(this.results));
                var arr = [];
            
                for (var i = 0; i < result.length; i++) {
                    console.log('1.5)'+(JSON.stringify(result[i])));
                    var rec = {};
                    var key = Object.keys(result[i]);
                    for( var j = 0; j < key.length; j++ ) {
                        console.log('key )'+ key[j] + ' -- ' + result[i][key[j]]);
                        rec[ key[j] ] = result[i][ key[j] ];
                        if(key[j]=='Account'){
                            rec['Account.Name'] =  result[i][ key[j] ].Name;
                        }
                    }
                    rec['LinkValue'] = '/' + result[i].Id;
                    arr.push(rec);
                 }
                 this.results =  arr;
                 this.data = arr;
                 this.recCount = arr.length;
                 console.log('2)'+JSON.stringify(this.results ));
            })
            .catch(error => {
                /*eslint no-console: "off"*/
                console.log(error);
                this.result = error;
                this.dispatchEvent( new ShowToastEvent({
                    title: '処理結果',
                    message: 'レコード取得が失敗しました。\n' + error.body.message,
                    variant: 'error',
                    mode: 'dismissable'
                }));
            })
            var sql2 = 'Select '
                + ' Count(Id)'
                + ' From TanakoManagement__c'
                + " Where Contact__r.AccountId = '" +this.recordId +"'";
               
                console.log('test 1 sql>' + sql2);
                commonSelectRecords({strSql: sql2 })
                .then(result => {
                    var arr = [];
            
                for (var i = 0; i < result.length; i++) {
                    console.log('1.5)'+(JSON.stringify(result[i])));
                    var rec = {};
                    var key = Object.keys(result[i]);
                    for( var j = 0; j < key.length; j++ ) {
                        console.log('key )'+ key[j] + ' -- ' + result[i][key[j]]);
                        rec[ key[j] ] = result[i][ key[j] ];
                        if(key[j]=='Account'){
                            rec['Account.Name'] =  result[i][ key[j] ].Name;
                        }
                    }
                    arr.push(rec);
                 }
                 this.results =  arr;
                 this.totalCount=arr[0].expr0;
                 console.log('3)'+JSON.stringify(this.results ));
                 console.log('3.5)'+JSON.stringify(this.totalCount ));
                })
                .catch(error => {
                    /*eslint no-console: "off"*/
                    console.log(error);
                    this.result = error;
                    this.dispatchEvent( new ShowToastEvent({
                        title: '処理結果',
                        message: 'レコード取得が失敗しました。\n' + error.body.message,
                        variant: 'error',
                        mode: 'dismissable'
                    }));
                })

    
        }
        sortColumns( event ) {
            this.sortedBy = event.detail.fieldName;
            
            this.sortedDirection = event.detail.sortDirection;
            if(this.sortedDirection==='asc'){
                this.sort='ASC';
            }else{
                this.sort='DESC'
            }
            var sql = 'Select '
                + ' Id, Sequence_Number__c,GW_Store_Number__c,'
                + ' STORE_NAME__c,'
                + ' CommonId__c,'
                + ' VM_Examination_Result__c,'
                + ' Company_Name__c'
                + ' From TanakoManagement__c'
                + " Where Contact__r.AccountId = '" +this.recordId +"'"
                + " order by Sequence_Number__c "+this.sort+" limit 5";
                console.log('test 1 sql>' + sql);
                commonSelectRecords({strSql: sql })
                .then(result => {
                    //console.log('test 1 result>' + result);
                   //this.results = result;
                    
                   // var a  = JSON.parse(JSON.stringify(this.results));
                    var arr = [];
                
                    for (var i = 0; i < result.length; i++) {
                        console.log('1.5)'+(JSON.stringify(result[i])));
                        var rec = {};
                        var key = Object.keys(result[i]);
                        for( var j = 0; j < key.length; j++ ) {
                            console.log('key )'+ key[j] + ' -- ' + result[i][key[j]]);
                            rec[ key[j] ] = result[i][ key[j] ];
                            if(key[j]=='Account'){
                                rec['Account.Name'] =  result[i][ key[j] ].Name;
                            }
                        }
                        rec['LinkValue'] = '/' + result[i].Id;
                        arr.push(rec);
                     }
                     this.results =  arr;
                     this.data = arr;
                     this.recCount = arr.length;
                     console.log('2)'+JSON.stringify(this.results ));
                })
                .catch(error => {
                    /*eslint no-console: "off"*/
                    console.log(error);
                    this.result = error;
                    this.dispatchEvent( new ShowToastEvent({
                        title: '処理結果',
                        message: 'レコード取得が失敗しました。\n' + error.body.message,
                        variant: 'error',
                        mode: 'dismissable'
                    }));
                })

            var reverse = this.sortedDirection !== 'asc';
            
            let arr = this.results;
            console.log('data -- ' + JSON.stringify(arr[0]));
            arr.sort(this.sortBy(this.sortedBy, reverse))
            this.data = [...arr];
            
            //return this.results;
        }    

        sortBy(fieldname, reverse, primer) {
            
            var key = primer ?
                function(x) {return primer(x[fieldname])} :
                function(x) {return x[fieldname]};
                reverse = !reverse ? 1 : -1;
            return function (a, b) {
                return a = key(a)?key(a):'', b = key(b)?key(b):'', reverse * ((a > b) - (b > a));
            }
            
        }

        handleSearchMem(event) {
            
            this.keyword = event.target.value;
            var term = this.keyword;
            var arr = this.results;
            console.log('term >' + term);
            var regex = new RegExp(term, "i");
            try {
                //console.log('arr: ' + JSON.stringify(arr));
                var tmpData = arr.filter(row=>regex.test(row['Sequence_Number__c'])
                            || regex.test(row['STORE_NAME__c'])
                    
                            );
                //console.log('tmpData: ' + JSON.stringify(tmpData));
                this.data = [...tmpData];
                this.recCount = this.data.length;
            }catch(e){
                alert(e);
            }
        }

        handleSearchDB(event){

            if (event.key !== 'Enter'){
                return;
            }
            this.keyword = event.target.value;
            var term = this.keyword;
            console.log('term >' + term);
            var serchWord='';
            if(this.keyword!=''){
                serchWord= "AND (Sequence_Number__c LIKE '%"+term+"%'"
                         + "OR STORE_NAME__c LIKE '%"+term+"%')";
            }
            var sql = 'Select '
            + ' Id,Sequence_Number__c,GW_Store_Number__c,'
            + ' STORE_NAME__c,'
            + ' CommonId__c,'
            + ' VM_Examination_Result__c,'
            + ' Company_Name__c'
            + ' From TanakoManagement__c'
            + " Where Contact__r.AccountId = '" +this.recordId +"'"
            + serchWord
            + "order by Sequence_Number__c "+this.sort+" limit 5";
            console.log('test 1 sql>' + sql);
            commonSelectRecords({strSql: sql })
            .then(result => {
            //console.log('test 1 result>' + result);
            //this.results = result;
                
            // var a  = JSON.parse(JSON.stringify(this.results));
                var arr = [];
            
                for (var i = 0; i < result.length; i++) {
                    console.log('1.5)'+(JSON.stringify(result[i])));
                    var rec = {};
                    var key = Object.keys(result[i]);
                    for( var j = 0; j < key.length; j++ ) {
                        console.log('key )'+ key[j] + ' -- ' + result[i][key[j]]);
                        rec[ key[j] ] = result[i][ key[j] ];
                        if(key[j]=='Account'){
                            rec['Account.Name'] =  result[i][ key[j] ].Name;
                        }
                    }
                    rec['LinkValue'] = '/' + result[i].Id;
                    arr.push(rec);
                 }
                 this.results =  arr;
                 this.data = arr;
                 this.totalCount = arr.length;
                 console.log('2)'+JSON.stringify(this.results ));
            })
            .catch(error => {
                /*eslint no-console: "off"*/
                console.log(error);
                this.result = error;
                this.dispatchEvent( new ShowToastEvent({
                    title: '処理結果',
                    message: 'レコード取得が失敗しました。\n' + error.body.message,
                    variant: 'error',
                    mode: 'dismissable'
                }));
            })
        }


}
```
```tanakoDtable.js-meta.xml
<?xml version="1.0" encoding="UTF-8"?>
<LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>55.0</apiVersion>
    <targets>
        <target>lightning__AppPage</target>
        <target>lightning__HomePage</target>
        <target>lightning__RecordPage</target>
    </targets>
    <isExposed>true</isExposed>
</LightningComponentBundle>
```
```CommonDmlUtil.cls
public with sharing class CommonDmlUtil {
    @AuraEnabled(cacheable=true)
    public static List<SObject> commonSelectRecords(String strSql){
        return Database.query(strSql);
    }
}
```
## 実装効果

### 昇順・降順切替
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2705822/5c5ceb2f-5cbc-b325-890c-c24b86f0e055.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2705822/e935b33e-7c7b-f329-016e-4091faf61252.png)

### 検索結果
表示内容から検索
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2705822/8c6e7547-9209-19c5-5617-3f2c110342cf.png)
DBから検索した結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2705822/3c2309be-149e-c67a-17fd-7d2d557d0299.png)

## まとめ
今所はまた未完成ですけど。目的としてはDBから条件相応しいレコードを決まった件数まで取り出し、表示内容が昇順・降順の順位変更ができる。表示内容から絞り込みとDBから絞り込み二種類の検索ができるというテーブルを作成したい