Question regarding SOQL

Question

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

Answer 1

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
}