Dynamic SOQL gives Id in LWC datable instead of object Name even using __r.Name

Question

I need a big help with my LWC Component, I'm using Dynamic SOQL but I need to show the Name of lookup field and even using __r it shows Id, here is my codes and the view :

Apex:

@AuraEnabled (cacheable=true)
    public static String getRelatedFieldsAndRecords(String negotiationId){
        Map<String, String> returnMap = new Map<String,String>();

        //get fields from FieldSet
        SObject sObj = (SObject)(Type.forName('Schema.'+ 'CLM_Representative__c').newInstance());
        List<Schema.FieldSetMember> lstFSMember = sObj.getSObjectType().getDescribe().fieldSets.getMap().get('CLM_Representative').getFields();

        //prepare SOQL query based on fieldAPIs 
        String query = 'SELECT ';
        for(Schema.FieldSetMember f : lstFSMember) {
            if(f.getFieldPath() == 'CLM_Account__c' || f.getFieldPath() == 'CLM_Contact__c') {
                String str = f.getFieldPath().Substring(0,f.getFieldPath().length()-1);
                query += str + 'r.Name, ';
            } 
            else 
            {
                query += f.getFieldPath() + ', ';
            }
        }

        query += 'Id FROM ' + 'CLM_Representative__c' ;
        query += ' WHERE CLM_Negotiation__c = ' + '\'' + negotiationId + '\'';

        //execute query
        List<CLM_Representative__c > lstRecords = Database.query(query);

        String JSONStringRecords = JSON.serialize(lstRecords);

        //prepare a map which will hold fieldList and recordList and return it
        returnMap.put('FIELD_LIST', JSON.serialize(lstFSMember));
        returnMap.put('RECORD_LIST', JSONStringRecords);

        return JSON.serialize(returnMap);
    }

JS:

connectedCallback(){
        getFieldsAndRecords({   strObjectApiName: 'Contact',
                                strfieldSetName: 'CLM_Legal_Representative',
                                negotiationId: this.recordId})
        .then(result=>{    
            let objStr = JSON.parse(result);      
            let listOfFields = JSON.parse(Object.values(objStr)[1]);
            let listOfRecords = JSON.parse(Object.values(objStr)[0]);

            let items = [];

            listOfFields.map(element=>{
                items = [...items ,{label: element.label, fieldName: element.fieldPath}];
            });

            this.columns = items; 
            this.records = listOfRecords; 
            this.totalRecords = listOfRecords.length;
            this.pageSize = this.pageSizeOptions[0];
            this.paginationHelper();
        })
        .catch(error =>{
            console.log('error',error);
            this.records = undefined;
        })

        getRelatedFieldsAndRecords({negotiationId: this.recordId})
        .then(result=>{    
            let objSelectedStr = JSON.parse(result);      
            let listOfSelectedFields = JSON.parse(Object.values(objSelectedStr)[1]);
            let listOfSelectedRecords = JSON.parse(Object.values(objSelectedStr)[0]);

            let items = [];

            listOfSelectedFields.map(element=>{
                items = [...items ,{label: element.label, fieldName: element.fieldPath}];
            });

            this.selectedColumns = items; 
            this.selectedRecords = listOfSelectedRecords; 
        })
        .catch(error =>{
            console.log('error',error);
            this.selectedRecords = undefined;
        })
    } 

LWC Component:

Answer 1

I can see that you use Database.Query() to query dynamically to get records however To get relationship fields you need to provide the actual reference for that field.

refer this example

String s = 'Select Id, Name, XYZ__r.Name from contact where Id = \'0031D00000KkQLKQA3\'';
System.debug(Database.query(s));

List<Contact> cList = Database.query(s);
for(Contact con : cList){
    system.debug(con.XYZ__r.Name);
}

In this above example when we loop through the result in Apex code and access the Name field of the related record, you are explicitly requesting the value of that field and the Apex runtime is automatically resolving the relationship for you. In this case you will get the Actual Name Instead of Id

In your Code you can try like this

 List<CLM_Representative__c > lstRecords = Database.query(query);


List<Object> dataTableRecords = new List<Object>();

for(CLM_Representative__c obj : lstRecords) {

    Map<String, Object> record = new Map<String, Object>();

    record.put('Id', obj.Id);
    record.put('Account', obj.Account__r.Name); //explicitly requesting value of the field
    record.put('Field1', obj.Field1__c); //any field
    record.put('Field2', obj.Field2__c); //any field
    dataTableRecords.add(record);
}

     //prepare a map which will hold fieldList and recordList and return it
        returnMap.put('FIELD_LIST', JSON.serialize(lstFSMember));
        returnMap.put('RECORD_LIST', JSON.serialize(dataTableRecords));

        return JSON.serialize(returnMap);

Note This above code is an example how we can explicitly request relationship field please feel free to debug and make changes

some reference

Get SOQL Query to get Account's Name Instead of the Id?

https://developer.salesforce.com/forums/?id=9062I000000IHlNQAW&_ga=2.25373876.88093845.1675885910-1372243647.1645281567