How Can I Use SOQL to Query Multiple Unrelated Objects?

Question

I have a Location object with a related list called Product Items on the Location. I would like to display the same Product Items on the Service Territory Member object.

• Service Territory Member has a master-detail to Service Territory.

• Service Territory Member has a lookup to Product Item and Location.

• Service Territory Location has master-detail to Service Territory.

• Service territory Location has a lookup to Location.

My first choice was to create a Product Item related list on the Service Territory Member, but it doesn't show up in the page layout related list section. So I'm trying and do it from code. The SOQL query is not returning any data. Is there a better way to write it?

public with sharing class ProductItemController {
    
    @AuraEnabled(cacheable=true)
    public static List<sObject> getProductItems(Id recordId){
        ServiceTerritoryMember stm = [SELECT Id, ServiceTerritoryId FROM ServiceTerritoryMember WHERE Id = :recordId];
        ServiceTerritoryLocation stl = [SELECT Id, LocationId FROM ServiceTerritoryLocation WHERE Id = :stm.ServiceTerritoryId];

        List<ProductItem> products = [SELECT Id, Quantity_Reserved__c, Quantity_Remaining__c, ProductName
                                     FROM ProductItem
                                     WHERE LocationId = :stl.LocationId]; 
        return products; 
    }
}

Answer 1

The Object relationship that you have described can be represented as given the image below.

Primary purpose of related lists is to display many associated records for a given record and the related lists are available in Salesforce for the following type of objects:

1. In the Master-Detail relationship, master object will have related lists for displaying child records (since, a single master record will be associated with many child records).
2. In a (1-to-many) lookup relationship, the object that is associated with many records will have the related lists. For example, Object A has 1-to-many relationship (or associated records) with Object B, then Object B is will be available as related list on Object A. It doesn't make sense to have a related list for Object A on Object B (just for the sake of displaying 1 related record).

Assuming that my interpretation of the schema design is correct, you could see that Product Items can be a related list on Location, Service Territory Member can be a related list on Product Items etc. But, Service Territory Member will not have any of the other objects as related list.

With the given schema diagram, you can see that a Service Territory Member record is not restricted to be associated with a Product Item which is strictly defined via Service Territory > Service Location > Location > Product Item (i.e. unless you have facilitated that via customization). In fact, there could one set of Product Item records associated via Service Territory, but a totally different set of Product Item records associated directly with Service Territory Member. I believe this is reason behind your SOQL query not returning any result set.

In your case, a Product Item record can have many associated Service Territory Member records, but a Service Territory Member will have exactly one associated Product Item record. You could do a simple query as follows, but that would return a single Product Item record (owing to the current schema design).

SELECT ProductItem__r.Id, ProductItem__r.Quantity_Reserved__c, ProductItem__r.Quantity_Remaining__c, ProductItem__r.ProductName 
FROM ServiceTerritoryMember 
WHERE Id = :recordId

IMO, you could simple display the associated Product Item lookup field in the Service Territory Member detail page instead.

On the contrary, if you are trying to display all Product Item records that are eligible to be associated with a Service Territory Member record, then you probably need to check if the correct data is in place already or not (because your code seems fine, although they could be done in 1 or 2 SOQL queries).