Query in Apex Class is duplicated every time wire decorator in LWC is triggered

Question

I'm still fairly new to LWC & Apex, so here goes:

I have a datatable and a lightning input field which is passing a searchKey variable to the wire decorator:

@wire(getAllChildRecordsByRecType, { field: '$fields',  parentObject: '$parentField',searchKey: '$searchKey' })

The desired result is for only the rows that contain the term entered to display, since the table is displaying a substantial number of records from one of the main objects we use in SF. We have an associated Apex class for which searchKey is an optional parameter. If it gets passed, the following additional clauses are added to the query:

String key = '\'%' + searchKey + '%\'';

String queryString = 'SELECT Id, Name, ' + field + ' FROM ' + parentFieldw + ' WHERE Opportunity.StageName = \'Unrealized\'';

    if(searchKey != null && searchKey != ''){
        queryString += 'AND '
        + '(Opportunity.Name LIKE ' + key + ' OR Opportunity.Investment_Record_Type_Name__c LIKE ' 
        + key + ' OR Member_Name_Formula__c LIKE ' + key + ' OR TeamMemberRole LIKE ' + key + ')';
    }

If I type in a term that is in one of the rows, In the web console I get the following error:

"duplicate field selected: Investment_Name_Form__c"

From the debug log in Salesforce, I can see that for some reason some of the columns in the query saved to queryString are getting duplicated:

SELECT Id, 
Name, 
Investment_Name_Form__c,
Investment_Record_Type__c,
Member_Name_Formula__c,
TeamMemberRole,
Investment_Name_Form__c,
Investment_Record_Type__c,
Member_Name_Formula__c,
TeamMemberRole 
FROM OpportunityTeamMember 
WHERE Opportunity.StageName = 'Unrealized' 
AND (Opportunity.Name LIKE '%D%' 
    OR Opportunity.Investment_Record_Type_Name__c LIKE '%D%' 
    OR Member_Name_Formula__c LIKE '%D%' 
    OR TeamMemberRole LIKE '%D%'
)

Is there a more rational way to ensure that the main query in queryString only gets run once when a change in searchKey triggers the wire decorator? Or am I missing something completely obvious? It ocurred to me before i went on vacation for the holidays that maybe I should only assign the select statement to queryString when the query is initially run to avoid it basically being concatenated with itself, but I wasn't sure if that would work.

Any help here would be awesome. I've seen this filtering implementation in a few places and am not sure what is different that is causing mine not to work as intended. Below is the full JS for the Datatable Component i'm working on:

import { LightningElement, api, wire, track } from 'lwc';
import { updateRecord, deleteRecord } from 'lightning/uiRecordApi';
import { refreshApex } from '@salesforce/apex';
import { NavigationMixin } from 'lightning/navigation';
import { ShowToastEvent } from 'lightning/platformShowToastEvent';
import getAllChildRecordsByRecType from '@salesforce/apex/RecordViewController.getAllChildRecordsByRecType';
import {
    loadStyle
} from 'lightning/platformResourceLoader';
import parentDataTableResource from '@salesforce/resourceUrl/parentDataTable';
import Construction_Holdback_Scheduled__c from '@salesforce/schema/Loan_Attribute__ChangeEvent.Construction_Holdback_Scheduled__c';
import Threshold from '@salesforce/schema/ReputationLevel.Threshold';

const DELAY = 300;

export default class DataTable extends NavigationMixin(LightningElement) {
    
    
    
    @track value;
    @track error;
    @track data;
    @track page = 1; 
    @track items = []; 
    @track data = []; 
    @track columns; 
    @track startingRecord = 1;
    @track endingRecord = 0; 
    @track pageSize = 5; 
    @track totalRecountCount = 0;
    @track totalPage = 0;

    @api recordId;
    @api childObjectName = '';
    @api parentField = '';
    @api title = '';
    @api modalHeader;
    @api displayButton;
    @api sortedDirection = 'asc';
    @api sortedBy = 'Sort__c';
    searchKey = '';

    result;
    draftValues = [];
    lastSavedData = [];
    buttonElement;
    childId;
    numOfChildren;
    defaultSortDirection = 'desc';
    fieldList = [];
    displayButton = true;
    actions = [
        { label: 'View', name: 'view', iconName: 'utility:preview' },
        { label: 'Edit', name: 'edit', iconName: 'utility:edit' },
        { label: 'Delete', name: 'delete', iconName: 'utility:delete' }
    ];
    @api columns = [
        // { label: 'Investment Id', fieldName: 'URL__c', type: 'url', wrapText: true, typeAttributes: { label: { 'fieldName': 'Investment Id' },"target":"_self" } },
        // { iconName: 'utility:custom_apps', type: 'action', initialWidth: 200, typeAttributes: { rowActions: this.actions } }
    ];
    @api moreColumns;
    colsArray;

    constructor() {
        super();
        Promise.all([
            loadStyle(this, parentDataTableResource),
        ]).then(() => {})
    }

    /**
     * @description:    wire service returns a list of child records
    */
    @wire(getAllChildRecordsByRecType, { field: '$fields',  parentObject: '$parentField',searchKey: '$searchKey' })
    wireList({ data, error }) {
        this.results = data;   // track the provisioned value for refreshApex
        if (data) {
            let colNames = this.colsArray.forEach(this.fieldLabels);
            let createRow = row => {
                return { ...row, colNames }
            };
            this.data = data.map(createRow);

            this.numOfChildren = this.data.length;
            this.error = undefined;
        } else if (error) {
            this.error = error;
            this.data = undefined;
            console.log('there was an error fetching data for data table ', error);
        }
    }

    /**
      * @description:    using the columns payload, build the row label and field name
      */
    fieldLabels(item) {
        return ('\'' + item.label + '\': ' + 'row.' + item.fieldName + ',');
    }

    /**
      * @description:    using the columns payload, return the field names for the query
      * ...this can be replaced in Spring 21' with FIELDS(ALL) in ChildViewController
      */
    get fields() {
        this.colsArray = this.columns;
        if (this.moreColumns) {
            let moreFields = JSON.parse(this.moreColumns);
            this.colsArray = this.columns.concat(moreFields);
        }
        this.colsArray.forEach(column =>
            column.fieldName ? this.fieldList.push(column.fieldName) : undefined
        );
        return this.fieldList.toString();
    }

    /**
    * @description:    decide if list should be displayed or not
    */
    get displayList() {
        return this.numOfChildren > 0 ? true : false;
    }
    
    /**
   * @description: function to save the records after inline editing
   */
    handleSave(event) {
        const recordInputs = event.detail.draftValues.slice().map(draft => {
            const fields = Object.assign({}, draft);
            return { fields };
        });

        const promises = recordInputs.map(recordInput => updateRecord(recordInput));
        Promise.all(promises).then(input => {
            this.dispatchEvent(
                new ShowToastEvent({
                    title: 'Success',
                    message: this.title + ' Updated!',
                    variant: 'success'
                })
            );
            this.draftValues = [];
            this.refresh();
        }).catch(error => {
            console.log('debtPositionList.js error: ', JSON.stringify(error));
        });
    }

    handleKeyChange(event) {
        // Debouncing this method: Do not update the reactive property as long as this function is
        // being called within a delay of DELAY. This is to avoid a very large number of Apex method calls.
        window.clearTimeout(this.delayTimeout);
        const searchKey = event.target.value;
        this.delayTimeout = setTimeout(() => {
            this.searchKey = searchKey;
        }, DELAY);
    }

    /**
     * @description:    behavior when clicking edit or delete
    */
    handleRowAction(event) {
        const action = event.detail.action;
        const row = event.detail.row;
        switch (action.name) {
            case 'view':
                this[NavigationMixin.Navigate]({
                    type: 'standard__recordPage',
                    attributes: {
                        recordId: row.Id,
                        actionName: 'view',
                    },
                }).then(url => {
                    this.recordPageUrl = url;
                });
                break;
            case 'edit':
                this.buttonElement.label = "Edit " + this.title;
                this.buttonElement.childId = row.Id;
                this.buttonElement.openModal();
                break;
            case 'delete':
                const rows = this.data;
                const rowIndex = rows.indexOf(row);
                rows.splice(rowIndex, 1);
                this.data = rows;
                deleteRecord(row.Id)
                    .then(() => {
                        this.dispatchEvent(
                            new ShowToastEvent({
                                title: 'Delete Successful',
                                message: 'Position Deleted.',
                                variant: 'success'
                            })
                        );
                        this.refresh();
                    })
                    .catch(error => {
                        this.dispatchEvent(
                            new ShowToastEvent({
                                title: 'Error deleting record',
                                message: error.body.message,
                                variant: 'error'
                            })
                        );
                    });
                break;
        }
    }



    /** 
     * @description: Used to sort the 'company' column 
     */
    sortBy(field, reverse, primer) {
        const key = primer
            ? function (x) {
                return primer(x[field]);
            }
            : function (x) {
                return x[field];
            };

        return function (a, b) {
            a = key(a);
            b = key(b);
            return reverse * ((a > b) - (b > a));
        };
    }

    /** 
     * @description: behavior called when clicking sort 
     */
    onHandleSort(event) {
        const { fieldName: sortedBy, sortDirection } = event.detail;
        const cloneData = [...this.data];

        cloneData.sort(this.sortBy(sortedBy, sortDirection === 'asc' ? 1 : -1));
        this.data = cloneData;
        this.sortDirection = sortDirection;
        this.sortedBy = sortedBy;
    }

    /**
     * @description: used here to update styles and labels
     */
    renderedCallback() {
        const style = document.createElement("style");

        if (this.displayButton == true) {
            this.buttonElement = this.template.querySelector('c-new-button');
        }

        style.innerText = ``;


        this.template.querySelector(".slds-box").appendChild(style);
    }





    /**
     * @description: handle cancel, remove draftValues & revert data changes
     */
    handleCancel(event) {
        //remove draftValues & revert data changes
        // this.data = JSON.parse(JSON.stringify(this.lastSavedData));
        this.draftValues = [];
        console.log("Canceled");
    }

    /**
     * @description: refreshes the data by rerunning the wire service
     */
    refresh() {
        this.buttonElement.label = "New";
        return refreshApex(this.results);
    }


    /**
     * @description: store changed value to do operations on save
     *              this will enable inline editing and show standard cancel save buttons
     */
    updateDraftValues(updateItem) {
        let draftValueChanged = false;
        let copyDraftValues = [...this.draftValues];
        copyDraftValues.forEach(item => {
            if (item.Id === updateItem.Id) {
                for (let field in updateItem) {
                    item[field] = updateItem[field];
                }
                draftValueChanged = true;
            }
        });
        if (draftValueChanged) {
            this.draftValues = [...copyDraftValues];
        } else {
            this.draftValues = [...copyDraftValues, updateItem];
        }
    }

    /**
     * @description: write picklist changes back to original data
     */
    updateDataValues(updateItem) {
        let copyData = [... this.data];
        copyData.forEach(item => {
            if (item.Id === updateItem.Id) {
                for (let field in updateItem) {
                    item[field] = updateItem[field];
                }
            }
        });
        this.data = [...copyData];
        this.refresh();
    }


    picklistChanged(event) {
        event.stopPropagation();
        let dataRecieved = event.detail.data;
        let updatedItem = { Id: dataRecieved.context, Type__c: dataRecieved.value };
        this.updateDraftValues(updatedItem);
        this.updateDataValues(updatedItem);
    }
      

}

Answer 1

Issue is this.fieldList never gets reinitialized. So if this already has a value and if get fields() { gets called again, this.fieldList will just start appending to its original list. There is no duplicate check available.

Instead of using fieldList as plain array []. Use a Set

fieldList = new Set();

And your fields getter will change to: (Change the name from fieldList to uniqueFields)

get fields() {
    this.colsArray = this.columns;
    if (this.moreColumns) {
        let moreFields = JSON.parse(this.moreColumns);
        this.colsArray = this.columns.concat(moreFields);
    }
    this.colsArray.forEach(column =>
        column.fieldName ? this.fieldList.add(column.fieldName) : undefined
    );
    return Array.from(this.fieldList).join(',');
}

This is to remove any duplicates which might get added in the fields variable from JS.

Idea is to just remove duplicates from the fieldList