You need to sign in to do that
Don't have an account?
irlrobins
Find all childless accounts
Hey all,
I'm trying to find out a way to determine which accounts records out of my 120K+ accounts have no child objects (contacts, cases, opps, etc).
I've tried using apex to determine this but I keep hitting governor limits.
Anyone got any suggestions?
Instead of using count() - aggregate query,
use a subquery, it would be better. since count() will count all the records and each count is equal to number of query row retrived. governor limit on number of row retrived by soql can be hit if there are huge records in your database.
In below example, it keep checking if it has each of child or not. if none of child are present, Process it (Make boolean field as true).
At a time batch can contain max 200 records, so collect the account ids in set and you can use pseude code like:
Just a Suggestion,
Hope it helps.
All Answers
Hi,
If the parent and child objects are related via master-detail relationship then you can use "Summary Fields". Otherwise (in case lookup relationships) you can have fields in parent object which will hold count of child object filled by triggers on child objects.
Also alternatively if its a one time exercise, then you can export the relevant data, and using excel fubctions like countif() and aggregate function also get the desired results.
Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.
You can create a batch apex class to find such accounts with no child records.
Use a boolean field in Account as "isChildExists", set it true for accounts with no child records.
Run the batch and now you can use below query to find the required accounts.
Hope it helps.
Create roll up summay fileds for each child on the account storing the sum of the childs present. Then query for the accounts where all the roll up summary fields are 0.
There are two ways to go here: a batch job or a visualforce page that is running with the readonly parameter (only works if you are not running dml on those accounts).
Your query should look something like the following:
If you want to get fancy you can even use schema.describe to get the list of children related to the account and generate this query dynamically. One caveat is that you can only have somewhere around 20 sub-queries in one query so if you have more than 20 child objects you will need to break this up somehow.
I think this would be the best approach, given the large amount of records to be assessed. I haven't used batch apex before though so I need to spend some time examing it and determine how to accomplish this.
In peusdo code:
Of course this would break the SOQL query limit, so i need to adapt to get it to work in batch form
This would only work if all children were in a master detail relationship
Instead of using count() - aggregate query,
use a subquery, it would be better. since count() will count all the records and each count is equal to number of query row retrived. governor limit on number of row retrived by soql can be hit if there are huge records in your database.
In below example, it keep checking if it has each of child or not. if none of child are present, Process it (Make boolean field as true).
At a time batch can contain max 200 records, so collect the account ids in set and you can use pseude code like:
Just a Suggestion,
Hope it helps.
I've created a Batch Apex class as follows:
I call this class in my main apex class like this:
The batch job runs and emails me a result. However I did get some error emails just before this saying:
caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop
Class.DataCleanUtil.execute: line 18, column 8 External entry point
Can anyone suggest improvements in my code to to prevent this error occuring?
Hello irlrobins,
I didn't get the error exactly.
but i suggest to move the query in the batch, somewhat like this:
Give it a try, may it help.
Ok bit of a boo boo on my part. When I first executed my batch job I realised that I had forgotten to limit my aggregate query to just one record. I immediately aborted the job and it was listed as aborted in the monitor. But in reality the job was still running and when it started processing I got some errors due to not limiting the results.
I corrected my query (adding Limit 1) and ran the job again. Because the first job was listed as aborted I presume the errors were from the second job. This is where I was wrong. The second job ran after the first completed and this time it worked without issue.
Many thanks for all the help guys, appreciate it.