Counting records using SOQL

Sorry for the lack of update.  The last few days I was studying hard for my Winter 13 Maintenance exam for both my Developer and Administrator certifications.  Sure glad I passed them both, and I'm now back to my blog.

Today I'm going for a smaller article but for me personally, a significant one.  It is one of those things that really reminds me I have to have a change of mindset when constructing SOQL queries. This example below is a really really simple example, but I have to admit, it took me awhile to get there.

Let's say, I have a Province table and a PostalCode table.  You want to display a list of provinces including the number of postal codes for each province.

I came from SQL Server background.  In the past, when I saw a request like that, I would think, ok, start from the Province table and then inner join to the PostalCode table and get the count.   The query would look something like that:

SELECT p.Name, Count(pc.*) AS NumPostalCodes
FROM Province p INNER JOIN PostalCode pc ON p.Id = pc.ProvinceId

My point is, I always think from Province table (the parent) towards the PostalCode table (the child), and not the other way around.  Of course in SQL it does not really matter because you could just swap Province and PostalCode around and you will get the same results.

Alas, in SOQL, you cannot have two table names in the FROM clause.  What could you do then?  It would be nice if I could do something like:

SELECT Name, (SELECT Count(Id) FROM Postal_Codes__r) 
FROM Province__c

however SOQL does not allow aggregate functions in subqueries.

Let's say instead of getting the count, you just want to dump an entire list of provinces and the related postal codes.  In SOQL you could do it like that:

SELECT Name, (SELECT Name FROM Postal_Codes__r) FROM Province__c

This is not bad but notice that this is not a linear list.  The second column of each row returned is in turn a result set.

If you want a simple linear result set, just like you would in the SQL world, what you want is:

SELECT Province__r.Name, Name FROM Postal_Code__c

This SOQL will give you a linear list of all provinces and their related postal codes.  Look!  The starting point of this SOQL is from the child table.

This is what I talked about earlier, you have to have a change of mindset when construting SOQL queries.  Once you get that, getting the count is simply a slight modification of the query statement:

SELECT Province__r.Name, Count(Name) NumPostalCodes 
FROM Postal_Code__c 
GROUP BY Province__r.Name

Again, like I said, this is a very simple example, but for me to get to the last SOQL it took me almost an hour.  It's just not natural for me to think from the child table when I encounter a request like that.  I hope you find this information useful.

Next time I want to talk about retrieving records across 2 tables via the junction table in a master-detail relationship.