Question 1:
You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.
Accounts This Year
SELECT ID,
(SELECT StageName, Effective_Date__c, Expiration_Date__c
FROM Opportunities
WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
Accounts Last year
SELECT ID,
(SELECT StageName, Effective_Date__c, Expiration_Date__c
FROM Opportunities
WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)
Question 2
You can get the count like this
Accounts This Year
SELECT count(Id) mycount
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
Accounts Last Year
SELECT count(Id) mycount
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)
Question 3
You can use the same method, just combine the Where clauses from question 1
SELECT ID,
(SELECT StageName, Effective_Date__c, Expiration_Date__c
FROM Opportunities
WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
And Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)
A parent-child subquery (also called a left outer join) that you're performing ends up returning a List<Assignment>
, not JSON. It only looks like JSON when you print it with a debug statement.
for(PermissionSet permSet :[<your query here>]){
// each permission set record has an embedded List<PermissionSetAssignment> with
// your query.
// Because that's true, the following line will compile
List<PermissionSetAssignment> children = permSet.Assignments;
}
You'd see the same thing if you tried to debug any other List
.
The nice thing about having the subquery return a List
is that you can simply call .size()
on it to get a count of the records.
// number of child records is simply the size of the list
permSet.Assignments.size();
The one gotcha to look out for is that after a certain, ill-defined, threshold (in number of child records), you may run into the following error
Aggregate Query has too many rows for direct assignment, use FOR loop
In that case, like the error says, you'd need to use a loop to iterate over all the child records for a given parent record. To get the number of child records in this case, you'd need to either increment some variable inside the loop, or store the child records in a collection (List
, Map
, or Set
), and you wouldn't be able to get the count until after that loop finishes.
Some example code of how to handle that situation would be
for(PermissionSet permSet :[<your query here>]){
// Pretend that we'd get an error if we try to access permSet.Assignments directly
// Declare a list to hold the children
// This list is re-created on every loop iteration (so Assignments for one
// PermissionSet won't be counted towards the number of Assignments for the next
// PermissionSet)
// If you need to keep the PermissionSetAssignment records for all PermissionSets,
// then you'd want to use a Map<Id, List<PermissionSetAssignment>> (keyed
// on the Id of the PermissionSet), and you'd want to declare it outside of all loops.
List<PermissionSetAssignment> children = new List<PermissionSetAssignment>();
for(PermissionSetAssignment permSetAssign : permSet.Assignments){
// Add the children to the list, one at a time
children.add(permSetAssign);
}
// Now that the (inner) loop is done, we can see how many children there are
system.debug('number of assignments: ' + children.size());
}
Best Answer
We're only allowed to traverse one level down the object hierarchy from the object used in the outer query. We also are not allowed to compare two fields to one another (the right-hand side always needs to be a value) So your query, as written, is impossible.
With your pared-down query, it doesn't appear that you are doing anything that requires your query to be based on
OrderItem
though. If you make your outer query based onOrder
instead, you could get this to work by using two parent-child queries.If your full query allows you to do something like that, you may still have issues with how the data is returned. It's true that your query will return both
Opportunity_Partner__c
andOrderItem
, but if you pick out a singleOrderItem
, you wouldn't be able to do something likemyOrderItem.Order.Opportunity_Partners__r
.If that's what you need to provide in the end, then you'd need to do some extra processing. Child records are stored as an embedded
List<SObject>
in the 'parent' record, and parent records are stored as an embeddedSObject
in the 'child' record. We can set the parent object reference. We can't set the child collection reference (we can call.add()
on it, but this appears to have no effect).So given my example query, we can make executing
myOrderItem.Order.Opportunity_Partners__r
possible by setting theOrder
parent reference.That approach also means that you aren't forced to do this in a single query. As long as you have one query that can cause the child record collection to be populated, you can cause another child record to be able to traverse up (to the parent) then down (to the cousin records, I guess I'd call them). If you're able to return JSON instead of SObject records, then you wouldn't even need a common parent.
ex.
About the only thing I'd keep in mind is that setting the parent reference (or tacking it on to some JSON) is that it'll increase the size of the response to this external system you mention (because the Opportunity and Opportunity_Partner__c records will be duplicated for OrderItems on the same Order).