Apex Code Development You need to sign in to do that
Don't have an account?
BrianH SOQL - Inner Select and Eliminating rows
I am using an inner select to retrieve Account and Asset data. What I want to do, is eliminate any row where the assets child object is empty.
For example:
Query = Select a.Name, a.Id, (Select Id, Name, Status From Assets WHERE Status = 'Purchased') From Account a
Results
ID Name Assets
1 Act1 (5)
2 Act2
3 Act3 (1)
What I would like is to have Act2 eliminated from the results since it doesn't have any assets matching my criteria. How canI do that?
I figured out a way to get what I need through the SOQL query. It isn't the most elegant solution and I have the potential to hit governor limits faster, but it works.
SELECT a.Name, a.Id, (SELECT Id, Name, Status From Assets WHERE Status = 'Purchased')
FROM Account a
WHERE Id IN (SELECT AccountId From Asset WHERE Status = 'Purchased')
All Answers
Brian
I did some testing and I can't find away to do this within the Query as a filter.
What you can do is something like this.
List<Account> accountsWithAssets = new List<Account>(); for(Account a : [select Id, Name, (select Id, Name, Status From Assets where Status = 'Purchased') from Account]){ if(a.Assets.size() > 0){ accountsWithAssets.add(a); } }
Mike,
I am currently using your suggested approach in code. However, in certain scenarios I am running into issues (has to do with paging). I was hoping there would be a way to do it through a query filter.
I figured out a way to get what I need through the SOQL query. It isn't the most elegant solution and I have the potential to hit governor limits faster, but it works.
SELECT a.Name, a.Id, (SELECT Id, Name, Status From Assets WHERE Status = 'Purchased')
FROM Account a
WHERE Id IN (SELECT AccountId From Asset WHERE Status = 'Purchased')