Apex Code Development You need to sign in to do that
Don't have an account?
Jim F.ax281 SOQL query filtering by grandchildren
I'm trying to get a list of accounts based on values in a grandchild object. Here's the query that I wrote (more or less):
SELECT name FROM Account WHERE id IN (SELECT Child__r.Account__c FROM GrandChild__c)
This gives me the message: The inner select field 'Child__r.Account__c' cannot have more than one level of relationships.
I've tried a number of permutations of this, without success. Does anybody know a way to create a single SOQL query like this?
Yes, using GROUP BY is the suggested way of finding distinct records. I don't think you have to use GROUP BY ROLLUP, though. Just GROUP BY should do the trick.
With the relationship limitations we have in place, this is really the only way to get what you want in SOQL with a single query.
Unfortunately, if your data set goes beyond the default batch size, you're out of luck. GROUP BY queries don't support our normal queryMore semantics.
I'd suggest filing an idea in the Idea Exchange.
All Answers
Are you filtering on grandchild in the inner select? if not, can't you just have the inner select query from child__c instead ?
I think I wasn't clear: I only want to return accounts which have grandchildren. In standard SQL I might write:
SELECT DISTINCT
a.Name
FROM
Account a
INNER JOIN
Child__c c
ON a.ID = c.Account__c
INNER JOIN
Grandchild__c g
ON c.Id = g.Child__c
I'm pretty sure you're going to have to flip this query around:
SELECT child__r.account__c FROM grandchild__c where child__r.account__c IN (SELECT id FROM Account)
Give that a shot.
I can do this, but I'll have a lot of duplicate values. I guess I can do the following:
Select Child__r.Account__r.Name from Grandchild__c GROUP BY ROLLUP(Child__r.Account__r.Name)
This will work, but I really wanted to include a number of other fields in the results, and while the new aggregation functionality is cool, this seems like overkill, especially considering that I'm dynamically generating these queries. Any other ideas?
Thanks, Jim
Yes, using GROUP BY is the suggested way of finding distinct records. I don't think you have to use GROUP BY ROLLUP, though. Just GROUP BY should do the trick.
With the relationship limitations we have in place, this is really the only way to get what you want in SOQL with a single query.
Unfortunately, if your data set goes beyond the default batch size, you're out of luck. GROUP BY queries don't support our normal queryMore semantics.
I'd suggest filing an idea in the Idea Exchange.
I marked this as the answer, because I think it's useful, but I'll think about putting into the Idea Exchange. Thanks a lot for your help!