Winter 18 <lightning:datatable> does not get values from a parent record

Question

I am testing the new <lightning:datatable>, it is really good, and is a good replacement for most of my use cases which today I use a custom list component that I have developed.

I am getting data from my server through apex method, to populate it into the <lightning:datatable> component. I can bind the returned list directly to the data attribute and if the column names are same as the field names, all data is bound correctly.

The problem occurs when I get data from a parent record. For example - I want the Account name of my contact.

This example can show the problem:

Component Markup

<aura:handler name="init" value="{!this}" action="{!c.doInit}"/>

<aura:attribute name="mydata" type="Object"/>
<aura:attribute name="mycolumns" type="List"/>
<aura:attribute name="sortedBy" type="String"/>
<aura:attribute name="sortedDirection" type="String"/>

<lightning:datatable data="{! v.mydata }"
                     columns="{! v.mycolumns }"
                     keyField="Id"
                     onrowselection="{! c.getSelectedName }"
                     onsort="{!c.updateColumnSorting}"
                     sortedBy="{!v.sortedBy}"
                     sortedDirection="{!v.sortedDirection}"
                     hideCheckboxColumn="{!false}"
/>

Component Controller

({
    doInit: function (component, event, helper) {
        component.set('v.mycolumns', [
                    {label: 'Id', fieldName: 'Id', type: 'id', sortable:"true"},
                    {label: 'First Name', fieldName: 'FirstName', type: 'text', sortable:"true"},
                    {label: 'Last Name', fieldName: 'LastName', type: 'text', sortable:"true"},
                    {label: 'First Name Hebrew', fieldName: 'First_name_Hebrew__c', type: 'text', sortable:"true"},
                    {label: 'Last Name Hebrew', fieldName: 'Last_name_Hebrew__c', type: 'text', sortable:"true"},
                    {label: 'Account', fieldName: 'Account.Name', type: 'text', sortable:"true"},
                    {label: 'Email', fieldName: 'Email', type: 'email', sortable:"true"},
                    {label: 'Phone', fieldName: 'Phone', type: 'phone', sortable:"true"}
                ]);

        var action = component.get("c.getRecordsFromDb");
        action.setParams({
            sObjectName: "Contact",
            commaDelimitedFieldNames: "Id,FirstName,LastName,First_name_Hebrew__c,Last_name_Hebrew__c,Account.Name,Email,Phone",
            filter: ""
        });
        action.setCallback(this, function(response){
            var state = response.getState();
            if (component.isValid() && state === "SUCCESS") {
                var data = response.getReturnValue();

                component.set("v.mydata", data);
            }
        });
        $A.enqueueAction(action);
    }
})

APEX Aura Enabled Method

@AuraEnabled
public static List<sObject> getRecordsFromDb(String sObjectName,
        String commaDelimitedFieldNames,
        String filter) {

    system.debug('\n\n\n\ngetRecordsFromDb sObjectName: ' + sObjectName + ', commaDelimitedFieldNames: ' + commaDelimitedFieldNames + ', filter: ' + filter + '\n\n\n\n');

    // create a dynamic soql to get the value of the field
    String sql = 'SELECT ' + commaDelimitedFieldNames + ' FROM ' + sObjectName;

    if (!String.isBlank(filter))
        sql += ' WHERE ' + filter;

    system.debug('\n\n\n\ngetRecordsFromDb sql: ' + sql + '\n\n\n\n');
    List<sObject> records = Database.query(sql);
    system.debug('\n\n\n\ngetRecordsFromDb: ' + records + '\n\n\n\n');

    return records;
}

The problem is with the Account.Name field - the <lightning:datatable> component does not display the column data.

I know that the data is returned ok, and I know that in the object that is returned, the Account is contained like an object (probably this is the problem):

Is there a good way to show parent record data with this component?

Answer 1

There is no way today to access data like foo.bar.baz. The consumer of datatable has to flatten their data(server side or client side). Its a valid use case to consider.

Answer 2

I just hit the same problem. The way I resolved it was by modifying the response and adding an additional key->value pair for each record before I assign it to the attribute. You should be able to get the idea from this snippet (I tailored it to suit your code, so you might need to make some modifications before you get it working):

var commaDelimitedFieldNamesArray = commaDelimitedFieldNames.split(',');
var data = response.getReturnValue();

// Go through each field
for (var i = 0; i < commaDelimitedFieldNamesArray.length; i++)
{
    var fieldName = commaDelimitedFieldNamesArray[i];

    // If the field name contains a '.' (dot), it's a relationship field (e.g. Account.Name)
    if (fieldName.indexOf('.') != -1)
    {
        // Break up the levels of parenthesis [Account, Name]
        var relationshipFields = fieldName.split('.');

        // Go through each record from the response
        for (var record in data)
        {
            // Grab the field value (first level), should be an object (record.Account)
            var fieldValue = record[relationshipFields[0]];

            // Keep going until you come to the last nested object - that should have the actual value (ultimately record.Account.Name)
            for (var j = 1; j < relationshipFields.length; j++)
            {
                if (fieldValue && relationshipFields[j])
                {
                    fieldValue = fieldValue[relationshipFields[j]];
                }
            }

            // Set another property on the record object with the actual value (record[Account.Name] = 'Actual Value')
            record[fieldName] = fieldValue;
        }
    }
}

component.set("v.mydata", data);

Answer 3

I think the problem here seems to be the ability to use a join fields(lookups) in the Database.query - as mentioned in docs

dynamic SOQL can’t use bind variable fields in the query string

To solve this you can initialize an sObject and get fields individually :

Example for get accountName from contact:

String queryString = 'Select Id,Account.Name From Contact';
Map<String,Object> output = new Map<String,Object>();
sObject[] records = Database.query(queryString);
    for(Sobject rec: records) {
        sObject accountObject = rec.getSObject('Account');
        String accountName  = (String)accountObject.get('Name');
        //new response output
        output.put('Id',rec.Id);
        output.put('AccountName',accountName);
    }
System.debug(output);

Answer 4

If you are still looking for a solution to this problem, you might want to check out the following post: https://iwritecrappycode.wordpress.com/2017/11/22/salesforce-lightning-datatable-query-flattener/ This features a wrapper class that handles the child-parent dilemma when working with the Lightning:datatable component. Hope that helps.

Answer 5

The solution below, flattens the data.

Apex Controller

public with sharing class ManageContactsController {

    @AuraEnabled
    public static list<Contact> getContacts()
    {
        return [select firstname, name, lastname, email, phone, Owner.name, Owner.Profile.Name, (select id, subject from cases limit 1 order by createdDate desc ) from contact];
    }
}

JavaScript Controller

({
   init: function (component, event, helper) {
        component.set('v.mycolumns', [
                {label: 'Contact Name', fieldName: 'Name', type: 'text'},
                {label: 'Phone', fieldName: 'Phone', type: 'phone'},
                {label: 'Email', fieldName: 'Email', type: 'email'},
                {label: 'Owner', fieldName: 'Owner_Name', type: 'text'},
                {label: 'Most Recent Case', fieldName: 'Cases_0_Subject', type: 'text'}
            ]);
        helper.getData(component, event, 'getContacts', 'mydata');
    }
})

Helper

({
    flattenObject : function(propName, obj)
    {
        var flatObject = [];

        for(var prop in obj)
        {
            //if this property is an object, we need to flatten again
            var propIsNumber = isNaN(propName);
            var preAppend = propIsNumber ? propName+'_' : '';
            if(typeof obj[prop] == 'object')
            {
                flatObject[preAppend+prop] = Object.assign(flatObject, this.flattenObject(preAppend+prop,obj[prop]) );

            }    
            else
            {
                flatObject[preAppend+prop] = obj[prop];
            }
        }
        return flatObject;
    },

    flattenQueryResult : function(listOfObjects) {
        if(typeof listOfObjects != 'Array') 
        {
            var listOfObjects = [listOfObjects];
        }

        console.log('List of Objects is now....');
        console.log(listOfObjects);
        for(var i = 0; i < listOfObjects.length; i++)
        {
            var obj = listOfObjects[i];
            for(var prop in obj)
            {      
                if(!obj.hasOwnProperty(prop)) continue;
                if(typeof obj[prop] == 'object' && typeof obj[prop] != 'Array')
                {
                    obj = Object.assign(obj, this.flattenObject(prop,obj[prop]));
                }
                else if(typeof obj[prop] == 'Array')
                {
                    for(var j = 0; j < obj[prop].length; j++)
                    {
                        obj[prop+'_'+j] = Object.assign(obj,this.flattenObject(prop,obj[prop]));
                    }
                }
            }
        }
        return listOfObjects;
    },
    getInfo : function(component, event, methodName, targetAttribute) {
        var action = component.get('c.'+methodName);
        action.setCallback(this, $A.getCallback(function (response) {
            var state = response.getState();
            if (state === "SUCCESS") {
                console.log('Got Raw Response for ' + methodName + ' ' + targetAttribute);
                console.log(response.getReturnValue());

                var flattenedObject = this.flattenQueryResult(response.getReturnValue());

                component.set('v.'+targetAttribute, flattenedObject);

                console.log(flattenedObject);
            } else if (state === "ERROR") {
                var errors = response.getError();
                console.error(errors);
            }
        }));
        $A.enqueueAction(action);
    }
})

Component

<aura:component controller="ManageContactsController" implements="forceCommunity:availableForAllPageTypes" access="global">

<aura:attribute name="mydata" type="Object"/>
<aura:attribute name="mycolumns" type="List"/>

<aura:handler name="init" value="{! this }" action="{! c.init }" />

<h3>Contacts (With Sharing Applied)</h3>

<lightning:datatable data="{! v.mydata }"
   columns="{! v.mycolumns }"
   keyField="Id"
   hideCheckboxColumn="true"/>

</aura:component>

Source

Answer 6

Writing the SOQL in the Apex controller using IN instead of relationships is another option.

I needed the names of Products in a particular Pricebook so started with

SELECT Product2.Name
FROM PricebookEntry
WHERE Pricebook2Id = '01s4x00000xxxxxxxx'

and hit the issue with the datatable not being able to access the related field.

Re-writing it as

SELECT Name
FROM Product2
WHERE Id IN (
    SELECT Product2Id
    FROM PricebookEntry
    WHERE Pricebook2Id = '01s4x00000xxxxxxxx'
    )

allows the Product2 Name field to be accessed directly.