+ Start a Discussion
Justin MitchellJustin Mitchell 

SOQL subquery syntax help needed

I need to query a list of accounts and their related contracts, but I need the accounts listed in order of the contract end dates.

So if Account_A has two contracts with end dates of 1/1/2020 and 1/1/2021
And Account_B has two contracts with end dates of 1/1/2019 and 1/1/2025

Then Account B needs to be on the top of the list because it has a contract which is ending sooner.
Is this possible to do in a single query?
Here's how the syntax would look in my mind if it helps explain what I'm trying to do. This query doesn't work though:
SELECT Id, Name, (SELECT Id, ContractNumber, EndDate FROM Contracts ORDER BY EndDate DESC)
FROM Accounts
ORDER BY Contracts[0].EndDate DESC NULLS LAST
Is this even possible?
Best Answer chosen by Justin Mitchell
jigarshahjigarshah
Try using the following free Appexchange package that helps create Rollup Summaries in non Master Detail relationship objects, which would prove of help in this case. You can then use the same in your query.

All Answers

jigarshahjigarshah
Justin,

You may not be able to achieve this within a single query since, the outer Order By clause within your Soql will refer to the Account records and not the associated child Contract fields.

A solution to your issue would be to create a new Rollup Summary field on Account, which holds the MAX() date value of the associated child Contract End Date values. You can then use that field within your Select and Order By Clause instead of Contracts[0].EndDate to sort the results.

Assuming you call the new field as MaxContractEndDate on Account, your query would now become
SELECT Id, Name, MaxContractEndDate (SELECT Id, ContractNumber, EndDate FROM Contract ORDER BY EndDate DESC)
FROM Account
ORDER BY MaxContractEndDate DESC NULLS LAST
Justin MitchellJustin Mitchell
Thanks for the reply. That's what I was starting to think. That would work wonderfully except you can't create Account to Contract roll-up summary fields. :(
User-added image

I'm also realizing now there will need to be some logic for which date is used. I want the OLDEST date that is not already in the past. In other words, the date that is coming up the soonest, after today.

I think I'm going to have to write a trigger if I want to track this, and have it sync up every time a contract is created or the end date field is updated. Which opens up a whole bunch of potential issues. UGH. :(

Either that or I need to find a better way to sort my visualforce list. Hmmm
jigarshahjigarshah
Try using the following free Appexchange package that helps create Rollup Summaries in non Master Detail relationship objects, which would prove of help in this case. You can then use the same in your query.
This was selected as the best answer
Justin MitchellJustin Mitchell
This is awesome! I've never seen this app before. This is going to be really useful. Thank you!