I'm stuck on this simple dilemma that I was hoping to get answered. I have 3 objects:

  • Quote
  • Quote_Year__c
  • QuoteLineItem

There is a Parent-Child relationship from Quote (Parent) to Quote_Year__c (Child) AND a Parent-Child relationship from Quote (Parent) to QuoteLineItem (Child).

I'm essentially trying to relate my QuoteLineItem records to my Quote_Year__c records. I know how to query the data so I can get the joined data properly. This query:

SELECT Id, (SELECT Id FROM QuoteLineItems), (SELECT Id FROM Quote_Years__r)
FROM Quote 
WHERE Id IN (SELECT QuoteId FROM QuoteLineItem) 
AND Id IN (SELECT Related_Quote__c FROM Quote_Year__c)

gives the following result: enter image description here

My dilemma is that I basically want an outer join on the QuoteLineItem records. So instead of having "72 total records" (i.e. the record count of my parent object), I'm wanting to have closer to 11,000 (the record count of my QuoteLineItem object). But... as far as I understand, I can't query on the QuoteLineItem object to get all the data I want since it, like Quote_Year__c, is a child to the Quote object. Thus, I can't use relationships to access the Quote_Year__c object from QuoteLineItem. If I'm wrong about that, please let me know, too.

Ok, so I have 2 possible paths I could take and I'm looking to get help on finding a solution to either one (no preference). This will all be done in an Apex class as well:

  1. I can use 2 different queries. I can query on the QuoteLineItem records and on the Quote_Year__c records separately (since both are children to the Quote object), then do an outer join on them, on the Quote Id, in my Apex class. There are a lot of records and this will be a high-usage class so is there an efficient way of doing an outer join on 2 SOQL queries without iteration mapping?

  2. I can manipulate the the query in a way (either in workbench, my Apex class, etc...) that allows me to perform an outer join on the Quote Id in one query instead of joining 2 separate ones. If that is possible, how could I go about doing that?

If anyone is feeling generous enough to offer solutions to both scenarios, I won't complain. :)

  • Is there ever more than one Quote_Year__c per Quote? – David Reed Oct 29 '19 at 20:16
  • Nope, that's a one-to-one relationship with the Quote record it's associated with. – Jacob Barazoto Oct 29 '19 at 21:04

Given that you mention in comments that there is only one Quote_Year__c per Quote, I see a couple of options here that might elide some complexity in extracting this data (and may also have benefits elsewhere in your application, like reporting, by avoiding an unnecessary child object).

  1. Combine the Quote_Year__c fields onto the Quote object. Then there's no need to do multiple subqueries; just query at the QuoteLineItem level and source the quote year information from its parent:

    SELECT QuoteId, Quote.Quote_Year__c FROM QuoteLineItem WHERE ...
  2. Use automation to populate a lookup field on Quote down to the Quote_Year__c. Then, likewise, your queries can just traverse another level of parent relationship, e.g.,

    SELECT QuoteId, Quote.Quote_Year__r.Name FROM QuoteLineItem WHERE ...

Either way, your queries will run, as you desire, on the QuoteLineItem, and you can easily traverse relationships to get the singular Quote Year associated with each line item.

I see child relationships where there's guaranteed to be only one child as an anti-pattern in most cases. There are a few use cases where it's the best of a collection of evils; based on the limited information about your application, I'm not convinced it's one of them.

| improve this answer | |
  • Thanks for the response @David Reed! Sadly, this situation requires that the Quote_Year__c object be separate from the quote object as it's a fairly crucial child object to a parent that really has no relation to quotes. In short, it's the shell structure of a multi-year contract system we're building out so the Quote_Year__c represents the year the client's contract is it. I should have been a bit more clear about that initially. Knowing that, any other thoughts regarding what you would do? – Jacob Barazoto Oct 30 '19 at 14:10
  • @JacobBarazoto Does that requirement eliminate proposal (2)? – David Reed Oct 30 '19 at 14:13
  • If it's not really possible to do, then yeah it would. I could see that option being simpler as it would only require manipulation within 1 query instead of querying twice then manipulating. However, it doesn't seem feasible so I guess option 1 would be the next best solution. – Jacob Barazoto Oct 30 '19 at 14:15

Ultimately, I learned that nested queries such as the one above are easily manipulated within Apex. The child-relationship's API name can be looped on as easily as one would loop through a list of lists. I hadn't realized this was possible in Apex so this issue was more caused by a lack of knowledge than by the necessity of a complex solution. :)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.