We have an object called "Compensation__c". This object contains the compensation a partner receives for selling a "Product2".

"Compensation__c" has a start date. Before this date this compensation is not valid yet. A "Product2" can have multiple compensation records scheduled in the future, so today I will get €1,- and I might get €2,- tomorrow for this same product and €1,50 next week.

I need to create a SOQL query which returns the current compensation record for a selection of products.

SELECT Id, Product__c, PartnerCompensation__c FROM Compensation__c WHERE CompensationType__c = :compensation AND StartDate__c <= TODAY AND Product__c IN :productIds

How do I select the most recent active compensation? I want to group by Product__c, but then all other fields must be aggregated as well. I do not want an average or max compensation, I want the actual compensation...


You can use a parent-child subquery (a.k.a. a Left Outer Join) to associate Compensation__c records to their related Product2 record.

This will allow you to filter Compensation__c records as you wish, using ORDER BY and LIMIT to grab just the most recent, appropriate record.

    (SELECT Id, PartnerCompensation__c 
        FROM Compensations__r 
        WHERE CompensationType__c = :compensation AND StartDate__c <= TODAY 
        ORDER BY StartDate__c DESC 
        LIMIT 1) 
 FROM Product2 
 WHERE Id IN :productIds]

Compensations__r is the child relationship name (which you should be able to see/verify by looking at the definition of your Product__c lookup field).

A subquery returns a List<SObject> embedded in the resulting Product2 records, and you'd access the Compensation__c records like this

for(Product2 prod :<the query>){
    // Subqueries result in fully-fledged SObject records
    // Once you pull a record out of the list, you can use it just like you would
    //   any other SObject.
    Compensation__c mostRecent = prod.Compensations__r[0];


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.