Lightning Datatable and Relationship Queries

The Lightning Datatable is a flexible component to show data in a sortable, actionable table. Formatting is automatic provided by the Lightning Design System. Data gets provided as JSON array.

The challenge

A prime use case for a data table is to show results returned via @AuraEnabled from a SOQL query. Ideally relationship identity fields should turn into links and data from relationship queries (something like MyCustomObj__r.Color) should be usable in the table as well.

The tricky part: Relationship fields are returned (IMHO properly) as JSON objects. Datatable can't deal with object values for their columns. A returned value might look like this (deliberately using a generic example):

[{
  Id: 'payloadid',
  Color__: 'Blue',
  Stuff__r: {
    Id: 'ToyId',
    Name: 'Teddy',
    Price__c: 34.5,
    Shape__r: {
      Size__c: 'XL',
      Geometry__c: 'round'
    }
  },
  Dance__c: 'Tango'
}];

Solution Part 1

Abhishek had an idea (see also Part 2) and we put our heads together. To be able to use such a structure in a data table, we need to flatten it out, so instead of having Stuff__r.Shape__r.Size__c we want a flat value Stuff__r_Shape__r_Size__c with simples values (String, Number, Date, boolean). This can be achieved entertaining a few lines of JavaScript running against the JSON returned from your backend query:

const theResponse = response.getReturnValue();
theResponse.forEach(row => {
  for (const col in row) {
    const curCol = row[col];
    if (typeof curCol === 'object') {
      const newVal = curCol.Id ? ('/' + curCol.Id) : null;
      flattenStructure(row, col + '_', curCol);
      if (newVal === null) {
        delete row[col];
      } else {
        row[col] = newVal;
      }
    }
  }
});

function flattenStructure(topObject, prefix, toBeFlattened) {
  for (const prop in toBeFlattened) {
    const curVal = toBeFlattened[prop];
    if (typeof curVal === 'object') {
      flattenStructure(topObject, prefix + prop + '_', curVal);
    } else {
      topObject[prefix + prop] = curVal;
    }
  }
}

With this script as part of your callback function you now have access to a flattened array with all the field values. You might want to modify the code to get rid of the __c and __r parts, so it is easier on the eye.

Solution Part 2

To show the relationship columns as links there are two simple steps:

• Define the data type as URL in the columns array type = 'url'
• Specify the typeAttributes to point to the field containing the label

A sample column definition (opening the link in a new tab/window) looks like this:

{
 label: 'Your Stuff',
 fieldName: 'Stuff__r',
 type: 'url',
 typeAttributes: {
   label: {
     fieldName: 'Stuff__r_Name'
   },
   target: '_blank'
  }
}

You could provide functionality to automatically generate the field definition array based on the SOQL query you execute, but that's another, Apex style, story for another time.

As usual: YMMV