0

I am checking if it is possible to construct a SOQL query using the below object structure.

I am using the out of the box WorkOrder object to query Account fields from the Account object.

I have a Custom_Object__c which has a lookup relationship to the Account object. I want to know if it is possible to query the Custom_Object__c fields keeping the FROM WorkOrder clause.

Here is the query I constructed so far -

SELECT Id, WorkOrderNumber, WorkOrder.Account.Name FROM WorkOrder

Now how do I include the fields of the Custom_Object__c onto the above query keeping the FROM WorkOrder clause.

1 Answer 1

3

Custom_Object__c which has a lookup relationship to the Account

It means, that Custom_Object__c is a child of Account object. That means, that there may be many children for a particular Account record.

Making, an assumption, that the parent field on Custom_Object__c to Account object is Account__c and the child relationship name is Custom_Objects__r

First, you can try parent-to-child relationship queries

SELECT Id, WorkOrderNumber, (SELECT Id, Name FROM Account.Custom_Objects__r) FROM WorkOrder

but it returns an error

First SObject of a nested query must be a child of its outer query.

so it is not possible to SELECT Id, Name FROM Account.Custom_Objects__r

It can't be achieved as limitation exists because SOQL does not support joins in the same way that SQL does; it only allows traversal through direct parent-child or child-parent relationships.


However, you can change the approach of your SOQL query to start from the Account object instead of WorkOrder. This allows you to access fields from both WorkOrder and Custom_Object__c

SELECT 
    Id, 
    Name,
    (SELECT Id, WorkOrderNumber FROM WorkOrders),
    (SELECT Id, Name FROM Custom_Objects__r)
FROM 
    Account
WHERE
    ...

Or if this is allowed for you, can be done in 2 SOQL queries

  1. SELECT Id, WorkOrderNumber, Account.Name, AccountId FROM WorkOrder
    
  2. collect AccountId to accountIds
  3. SELECT Id, Name, Account__c FROM Custom_Object__c WHERE Account__c IN :accountIds 
    
1
  • Thanks Oleksandr for the detailed explanation.
    – sumchans
    Commented Dec 12, 2023 at 16:27

You must log in to answer this question.

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