+ Start a Discussion
Jim F.ax281Jim 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?

Best Answer chosen by Admin (Salesforce Developers) 
dkadordkador

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

SuperfellSuperfell

Are you filtering on grandchild in the inner select? if not, can't you just have the inner select query from child__c instead ?

Jim F.ax281Jim F.ax281

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

dkadordkador

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.

Jim F.ax281Jim F.ax281

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

dkadordkador

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.

This was selected as the best answer
Jim F.ax281Jim F.ax281

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!