0

I am having trouble with the output of an SOQL multilevel relationship query:

SELECT
Flight__c.Related_Case__r.Related_Success_Story__r.Sharing_Permission__c,
Flight__c.Related_Case__r.Related_Success_Story__r.Sharing_Notes__c
FROM Flight__c

Flight__C is the grandchild

Case is the Parent

The query runs fine but it returns a single column called – Related_Case__r.Related_Success_Story__r

It contains the results from BOTH fields – Sharing_Permission__c and Sharing_Notes__c in the same column.

I don’t understand why this isn’t returning two separate columns for each field. I am noticing that whenever I try to create a relationship query of over two levels the column output does not include the field name, just the relationship path.

3
  • 1
    Where are you consuming this query. It does return both fields. But if you were consuming the record in JSON or some other areas, the field path would consolidate.
    – Adrian Larson
    Sep 28, 2022 at 14:16
  • or if you run it from Developer Console it also shows as 1 column (while there's actually data for both in the query result) Sep 28, 2022 at 14:18
  • I ran it in the Developer Console but I am trying to get it to export into Google Sheets and it is returning the data from both fields into the one column. The issue is that I am trying to fix something that was done previously and at one point it was exporting into Sheets as separate fields but when I use the same query that was used previously it doesn't.
    – pcmm
    Sep 28, 2022 at 14:41

1 Answer 1

1

This is both the result of design desicions made by Salesforce, and an artifact of whatever approach you're using to turn the data into a string.

Some background

If you have experience with other programming languages and have used a database in a project before, it shouldn't be a surprise that there is (in many cases) a difference in how programming language represents/works with data, and how a database represents/works with data. ORMs (Object-Relational Mappers) help hide the complexity in going to/from the database, and let us (programmers) just focus on working with data instead of trying to transform it.

For all of the complaints that we might have with Salesforce/Apex, one thing I think they've done exceptionally well is integrating SObjects (i.e. database tables), queries (mainly through SOQL and SOSL), and code. There's definitely some ORM-style mapping happening somewhere, but it's transparent to us.

What this means for us

Data on related records (e.g. the Name of an Account that an Opportunity is related to through the AccountId field) gets stored in an embedded SObject when you are querying from a child record to a parent record (e.g. Opportunity -> Account, the "child" is always the one that contains the relationship field),
and in an embedded List<SObject> when you're querying from a parent record to child record(s).

They're always fully-fledged SObjects (or a list thereof), which allows us to access the data in the same way as if you were working through any other "Object" (in the "Object-Oriented Programming" sense), dot notation.

The way you'd access the data is exactly the same way that you queried it. E.g. your query has Related_Case__r.Related_Success_Story__r.Sharing_Permission__c, so when you go to use the result of your query, you'd access that data like myFlight.Related_Case__r.Related_Success_Story__r.Sharing_Permission__c

Same goes for Sharing_Notes__c.

Since these are nested objects, whenever you want to do something like a system.debug(), Salesforce needs to know how to turn your object(s) into a printable string (a process called "serialization"). The default serialization approach will leave out data on related records and collapse it down to just the __r "field" (but it's really an SObject). Something similar also happens when you execute a query through the developer console.

The data is still there, and you can still use it.

If you want a serialization that includes more (all) of the related data, then the easiest option is probably to serialize the query result as JSON. E.g.

Opportunity opp = [SELECT Id, Name, StageName, Account.Name, Account.CreatedDate FROM Opportunity WHERE Id = '006000000000001'];
system.debug(JSON.serialize(opp));
1
  • My issue is that this same query was used to export data into a Google Sheet and it apparently gave the output in two different columns as of a few months ago. I am just trying to recreate it but I cannot get that to happen.
    – pcmm
    Sep 28, 2022 at 14:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .