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:
- 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).
- 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
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 >
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
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).