3

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])

1 Answer 1

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
}
3
  • 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, 2015 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, 2015 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, 2015 at 17:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .