2

In SOQL, is it possible to say

LIMIT 1 object1 record FOR EVERY 1 object2 record?

Example) Every Account has Property__c lookup relationship. I want SOQL to return a list of the most upcoming Account (using Account.Arrival_Time__c) FOR EACH property.

So I would like a Query where each Property is only returned ONCE, and returns a SINGLE Account to match. What I have currently, is a SOQL query that returns several upcoming accounts for each Property__c record.

 for (Account acc : [SELECT Id, Name, Arrival_Time__c, Property__c FROM Account WHERE Arrival_Time__c >= :Datetime.Now() AND Property__c 
                     IN :listOfProperties ORDER BY Arrival_Time__c ASC])
6

Your best bet is probably to have your initial query on the Property__c object and include a sub-query on child Account records. Something like the following should work:

select Id,
    (select Id, Name, Arrival_Time__c, Property__c from Account
        where Arrival_Time__c >= :DateTime.now()
        order by Arrival_Time__c asc limit 1
    )
from Property__c where Id in :listOfProperties

This should return a list of Property__c records with up to 1 child Account record per Property__c, accessible as a list via the Child Relationship Name of that lookup field (most likely Accounts in this case).

So your updated Apex might look something like:

List<Account> accs = new List<Account>();
for ( Property__c prop : [select Id, (select Id, Name, Arrival_Time__c, Property__c from Account where Arrival_Time__c >= :DateTime.now() order by Arrival_Time__c asc limit 1) from Property__c where Id in :listOfProperties] ) {
    if ( prop.Accounts != null && !prop.Accounts.isEmpty() ) {
        accs.add(prop.Accounts[0]);
    }
}
for ( Account acc : accs ) {
    // do whatever you were doing in your initial loop provided in the question
}
  • FYI, prop.Accounts will never be null. If there's a significant number of records to process, simply removing that condition will improve performance considerably. – sfdcfox Oct 27 '15 at 17:19
  • True, I tend to go a little overboard with null checks because of a bad experience with a NPE on my first deployment way back when! – JCD Oct 27 '15 at 17:21
  • 1
    You, and about every single developer that's ever started on the platform. It'd be nice if there was a more concise list of where NPE can occur. – sfdcfox Oct 27 '15 at 17:40

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.