SOQL Return Value Data Types When Used In Apex Code

Today in class we wrote an Apex trigger that resulted in my learning some interesting quirks about data types when embedding SOQL statements into code written in SalesForce's Apex language.

"TL; DR" summary: SOQL query results can be typecast into the object in their outermost FROM, or into a list thereof, unless the outermost SELECT involves an aggregation.

 

In class, we wrote code that looks like this:

...
for(Session_Speaker__c newItem : trigger.new {

Session_Speaker__c sessionSpeaker = [SELECT Session__r.Name, Speaker__r.First_Name__c, Speaker__r.Last_Name__C FROM Session_Speaker__c WHERE Id=:newItem.Id];

.../* Do stuff */ }
...

For anyone who can't already read this:

The above code loops through each "Session Speaker" record that has recently been inserted into the database (it's an 'after insert'-typed trigger). It uses a holding variable called "newItem" to store the entirety of the record currently being processed. Each time it executes the code inside the loop, it does an SOQL query against ALL rows of the entire database stored in the "Session Speaker" object (table) and sees if any of them have an Id with the same value as the "SessionSpeaker" record currently being examined by the for-loop. If it finds such a "Session Speaker" record, it stores a specialized copy of it in yet ANOTHER holding variable, this one called "sessionSpeaker."

I say "specialized copy" because it doesn't include all of the fields of "Session Speaker" and even throws in a few extras from an object that acts as a "master" to SessionSpeaker (the fields referred to with the MasterObjectName__r.FieldFromTheMasterRecord__c syntax).

SOQL is good at making such "specialized copies" of rows from SalesForce objects - a lot like formula fields are good at making "specialized" single fields. If you're a database programmer, I'm guessing you even try to put as much of your algorithm into the SOQL parts of your triggers as you can.

 

Anyway, both "holding" Apex variables, newitem and sessionSpeaker, have a data type of "Session Speaker object" or "Session_Speaker__c."

Handling the "data type" returned from the SOQL query, as you store it in Apex, is pretty simple.

• When you perform a SOQL query whose outermost FROM is the "Session Speaker" object, you can store it in a "Session_Speaker__c"-typed Apex variable as long as it only returns one row.
• If it returns more than one row, you have to store the SOQL query's output into one of Apex's typed collection classes, such as "List<Session_Speaker__c>," and iterate through or aggregate the list.

 

Only not quite.

Today, Andy Boettcher taught me that if the outermost part of your SOQL query includes an aggregation in the SELECT, the query's return value's cannot be typecast into a List<YourObjectTypeHere__c>-typed or YourObjectTypeHere__c-typed Apex variable.

SOQL queries with aggregations in the outermost query insist to any Apex code waiting to capture them that they are List<AggregateResult>-typed. Not even something like AggregateResult if there's just one row in the result. Just List<AggregateResult>.

 

Here's an example of an SOQL query with an aggregation in the outermost SELECT:

[SELECT ContactId, count_distinct(OpportunityId) FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in

(SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR')

GROUP BY ContactId]

(I'm planning to use it to drive an "is working with the admissions department?" formula field on Contact that can be exposed to other departments.)

 

List<AggregateResult> variables require for loops and processing with a .get() method. You can't just "dot-notate" the field you want to retrieve like you can with a "Session_Speaker__c"-typed variable. Sample code coming soon.