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.

Strange behaviour of apex:column

Let's say you have a custom field, say Age, on a custom object, say Artist, which is a numeric field with 0 decimal places.  If you want to display it in a table showing a list of artists, normally you would do something like this:


<apex:page standardController="Artist__c" recordSetVar="artists">
<apex:pageBlock >
  <apex:pageBlockTable value="{!artists}" var="c">
    <apex:column>
      {!c.Name} 
    </apex:column>
    <apex:column>
      {!c.Age__c}
    </apex:column>
</apex:pageBlockTable>
</apex:pageBlock>  

However, in the display you'll find out your Age column is displayed as a field with 1 decimal place.  (Interestingly, I looked at the NumberOfEmployees field, a numeric field in the standard Account object, and it does not have that problem.)  It can be understood because after all, all numeric fields in Salesforce are decimal fields.  (If you go back to the set up menu and create a field, you only specify a Numeric field and then define the number of decimal places.  So that means Salesforce does not distinguish between a decimal field and an integer field.  All numeric fields are decimal fields.)

So to circumvent that, all you need to do is to use the Round function.

    <apex:column>
      {!Round(c.Age__c, 0)}
    </apex:column>

Now, there is a value attribute for <apex:column> which you can also use.  So the above can be written as:
    <apex:column value="{!c.Age__c}">
       
Well, what do you know.  This time, the Age is displayed properly with no decimal points.  Not only that, the column, via the use of value attribute, will display the column header.

So, again, with this code,

<apex:page standardController="Artist__c" recordSetVar="artists">
<apex:pageBlock>
  <apex:pageBlockTable value="{!artists}" var="c">
    <apex:column >
      {!c.Name} 
    </apex:column>
    <apex:column >
      {!c.Age__c}
    </apex:column>
    <apex:column >
       {!Round(c.Age__c, 0)}
    </apex:column>
                
    <apex:column value="{!c.Age__c}"/>  <!-- this displays the Age correctly -->
                
  </apex:pageBlockTable>
</apex:pageBlock>    
</apex:page>


you'll get this:

Notice that when you use the value attribute, the column header is automatically displayed.

Now, what if the field has some decimal places?

Let's say we also have a Height field with 3 decimal places after the decimal point.  Let's also say for simplicity, all male artists are 1.80m tall and all female artists are 1.814m tall.

If you replace all the Age__c above with Height__c, you'll see a new table.  This time, however, worse yet, you see 1.8 and not 1.800 for the male artists.  This time even the Round function used in the second height column does not help matter.  The code is this,


<apex:page standardController="Artist__c" recordSetVar="artists">
<apex:pageBlock>
  <apex:pageBlockTable value="{!artists}" var="c">
    <apex:column >
      {!c.Name} 
    </apex:column>
    <apex:column >
      {!c.Height__c}
    </apex:column>
    <apex:column >
       {!Round(c.Height__c, 3)}
    </apex:column>

    <apex:column value="{!c.Height__c}"/>  <!-- again, this displays the Height correctly -->
                             
  </apex:pageBlockTable>
</apex:pageBlock>    
</apex:page>


and this is the screen display.

But once again, the value attribute for <apex:column> shows the correct answer.

Now, finally, to the part that really got me into writing this article.  From the above, you may feel that the value attribute is the right way to go because it seems to give the most desirable display of field values.  Now, what if you want to round the column so it only shows 2 decimal places?

Sure, wrap your formula expression for the value attribute with the Round function call, so the last line of the code is:

    <apex:column value="{!Round(c.Height__c, 2)}"/>

Let's run it!

Oh no, an error!

There is no syntax error for sure.  You know Salesforce won't even let you save your code unless it's syntactically correct.  The above change saves successfully.  The error really does not make any sense.  This to me looks like a bug in Salesforce.

With my experimentation, to successfully display the value correctly, you may still want to stay away from the value attribute.  Instead, make use of a combination of <apex:outputText> and <apex:param> to format your data.  This is the final code (Note:  you will have to use the headerValue attribute to specify the column name this way).

<apex:page standardController="Artist__c" recordSetVar="artists">
<apex:pageBlock>
  <apex:pageBlockTable value="{!artists}" var="c">
    <apex:column >
      {!c.Name} 
    </apex:column>
    <apex:column >
      {!c.Height__c}
    </apex:column>
    <apex:column >
       {!Round(c.Height__c, 3)}
    </apex:column>

    <apex:column value="{!c.Height__c}"/>  <!-- again, this displays the Height correctly -->
                    
    <apex:column headerValue="Height Column">
      <apex:outputText value="{0, number, #0.00}">
        <apex:param value="{!c.Height__c}"/>
      </apex:outputText>
    </apex:column>
                                
  </apex:pageBlockTable>
</apex:pageBlock>    
</apex:page>

and this is the final output.  The last column is what I really want.

Converting a List (of sObjects) to a Map

This article assumes you already know Salesforce's powerful way to collect objects, List, Map and Set.  If you are not familiar with them, you should go to the documentation for more information first.

What I found somewhat undocumented (it actually is documented but you don't find its usage this way in a lot of places) is a way you can initialize a Map collection.  You probably already know Map is used to provide a mapping between a primitive type (such as Integer, String, etc. used as the key) and practically any other type (used as the value).  A simple example is:

Map<Integer, String> myMap1 = new Map<Integer, String>{ 1 => 'a', 2 => 'b'};

The above declares a map variable, called myMap1 which contains 2 elements.  The first element contains a mapping from the integer 1 to the string a, and the second element contains a mapping from the integer 2 to the string b.

The "value" does not have to a primitive type - it can be an sObject, for example.

Map<Id, Account> myMap2 = new Map<Id, Account>();

The above is a variable myMap2 that stores a mapping between Id and the account sObject.

If you have run a SOQL statement and has retrieved a list of contacts, such as:

List<Contact> myContactList1 = [SELECT Id FROM Contact];

you can then "convert" this list to a map, by doing this:

Map<Id, Contact> myMap3 = new Map<Id, Contact>(myContactList1);

The new operator will convert this list (myContactList1) to create the map variable myMap3.  The key of myMap3 is obviously the Id field because that is what is selected.  In fact, Id is always used as the key in this context.  It is also always implicit.  So even if your SOQL had been:

List<Contact> myContactList2 = [SELECT Title FROM Contact];

the key will still be the Id field of Contact.  I think the Id will be based on what sObject you are selecting from.  In this case, it is the Contact, and therefore the Id will be the id of contacts.

The value part, however, is dependent on what you "select".  The list myContactList1 only selects the Id field, so the value only contains the Id field as well.  The list myContactList2 however, selects the Title field, so you can actually access the Title field via this myContactList2 collection.

Let's say you have a contact named Tim Barr.  If you run the following statements, you will see that in the debug log, the value for this contact contains the Id as well as the Title.

Contact c = [SELECT Id from Contact where FirstName = 'Tim' and LastName='Barr'];

List<Contact> myContactList2 = [SELECT Title FROM Contact];

Map<Id, Contact> myMap3 = new Map<Id, Contact>(myContactList2);

system.debug(myMap3.get(c.Id));

It seems to be the Id field is again implicit in the value too.  The SELECT clause determines what other fields are available in the value.