0

i'm new to SOQL and SF, so bear with me :) I have Sales_Manager__c object tied to Rent__c via Master-Detail relationship. What i need is to get Manager with highest number of Rent deals for a current year. I figured out that the Rents__r.size column stores that info. The question is how can i gain access to the size column and retrieve highest number out of it? Here is the picture of query i have My SOQL code

SELECT (SELECT Id FROM Rents__r WHERE StartDate__c=THIS_YEAR) FROM Sales_Manager__c

1 Answer 1

2

size here is not a column in the sense of a field that you can access and filter on directly in SOQL. It appears in your screenshot a property of a list that's being shown to you by whatever query tool you're using. Additionally, when you query data in Apex, it is a method (List.size()) that you can call on the property that represents the child objects you queried (which here would be called Rents__r).

However, unless your data volume is very small, performing a parent-child query like this one and then iterating over it in Apex to find the greatest value of Rents__r.size() is very inefficient and likely to run into limits issues.

There's a way you can find Manager with the highest count of Rent child records directly in SOQL, with no iteration required: perform an aggregate query on the child object, using a GROUP BY and LIMIT clause to pull out the parent object with the greatest child count. Since aggregate queries just got "cheaper" in Summer '18, it's also limits-efficient.

It'd look something like this:

SELECT Sales_Manager__c, count(Id) 
FROM Rents__c
WHERE StartDate__c = THIS_YEAR
GROUP BY Sales_Manager__c
ORDER BY count(Id) DESC
LIMIT 1

That'll yield the single Manager record Id with the highest count of rents, and the count of Rent objects (count(Id) is a way of expressing the count of records in a SOQL aggregate query). Note that you'll need to replace Sales_Manager__c in the SELECT clause with the name of the lookup field on the Rent__c object.

See SOQL Aggregate Functions and surrounding documentation for more information on using aggregate functions and queries.

If you're using this in Apex, note that the return value of an aggregate query is a List<AggregateResult>, rather than a List<someSobjectOrOther>. You can use myAggregateResult.get('expr0') to get the value of the first selected field (and increase the index for each successive field), or you can use aliasing to assign an explicit name:

List<AggregateResult> ars = [SELECT Sales_Manager__c manager, count(Id) count ...];
for (AggregateResult ar : ars) {
    System.debug(ar.get('manager'));
}
4
  • Wait, I thought that you could call .size() on an embedded child object list. I know that it'll give you issues if you end up querying too many child records, but it is possible since the in-memory representation is literally a List<Sobject>, no?
    – Derek F
    Jun 16, 2018 at 20:56
  • You certainly can, Derek - sorry if I did not make that clear. But it's not an efficient way to find the parent record with the greatest child count.
    – David Reed
    Jun 16, 2018 at 20:57
  • @DerekF I hope my edit makes that issue clearer.
    – David Reed
    Jun 16, 2018 at 21:01
  • Thanks David for great explanation and example, it totally work for me. Jun 17, 2018 at 5:32

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.