Using SOQL Subquery to Count Child Records

Short post, just wanted to post a real world application of previous post I made, Subquerying in SOQL.

I recently extended the Product2 object with an object that I would have LIKED to be a master-detail relationship, but since that cannot occur with a Product2, I had to emulate some functionality (see the edit at the bottom of this post for a correction). Ideally I’d create a rollup COUNT() field on product that summed the number of Child__c objects. To do this, I created a number field and the following trigger on Child__c:

trigger PopulateChild on Child__c (after insert, after delete) {
	List childList = Trigger.isInsert ? Trigger.new : Trigger.old;

	List childIds = new List();
	for (Child__c p : childList) {
		childIds.add(p.Product__c);
	}
	
	List products = [
			select
				id,
				(select id from child__r),
				child_count__c
			from
				product2
			where
				id in :childIds];

	for (Product2 product : products) {
		product.child_count__c = product.child__r.size();
	}
	update products;
}

Hacktastic!

*edit 2010-12-01* So this example fell apart, and there is some misinformation contained. You can have a custom object with a custom field that is a master-detail to a standard object. What you cannot do is have a standard object have a custom field that is a master-detail to a custom object. That is what my use case contained, and in copying the details over and changing names, some objects swapped. The principle of emulating a count rollup with a lookup field, a trigger and a SOQL Subquery are still correct, however.

This entry was tagged , , . Bookmark the permalink.

3 Responses to Using SOQL Subquery to Count Child Records

  1. Christine says:

    Thanks, Ray! Do you have an example for how this code would look if you are trying to count how many child Accounts were connected to a Parent Account? …. and then stamp the parent account with that number?

  2. Mustafa Turab Ali says:

    Why not use a rollup summary field to show count of child objects instead?

Comments are closed.