Welcome back to another episode of “Cooking with Code” where I introduce delectable bite-sized topics related to coding in Salesforce.

Today, we are concluding our satisfying sampler of Salesforce Object Query Language (SOQL) with Part Three of this three part series.

My first post introduced the basics of SOQL statements, including the following concepts and keywords: SELECT, FROM, LIMIT, ORDER BY, basic WHERE conditions, and basic operators. My second post showed how to pull data from related standard and custom objects.

In this, my third and final post, we will delve deeper still into: aggregates, how to work with dates and times, and using advanced operators with wildcards.

Ooooh wheee! This is going to be fun! Let’s have at it!

Creating aggregate data

One of the cool things you can do with SOQL is to use it to calculate aggregate data. As Salesforce admins, we use aggregate data all the time, when we create summary reports or roll-up summary fields.

The types of aggregate values we can calculate with SOQL are: MIN(), MAX(), AVG(), SUM(), COUNT(), and COUNT_DISTINCT().

Most of these are pretty straightforward, but COUNT() and COUNT_DISTINCT() could use a little more explanation.

COUNT() and COUNT_DISTINCT()

COUNT() returns the number of rows within your query:

SELECT COUNT() FROM recipe__c

The SOQL code above will query Salesforce and return the number of rows in our recipe__c object.

COUNT_DISTINCT() returns the number of distinct (e.g., not duplicative) value in our query based on a particular field. The SOQL code below will query Salesforce and return the number of rows in our recipe__c object that have unique values in the name field.

SELECT COUNT_DISTINCT(name) FROM recipe__c

Imagine we had the following rows in the recipe__c object:

COUNT() would return a record count of 7, while COUNT_DISTINCT(name) would return only 6. This is because “Hot Cross Buns” appears in the name field of two records.

Note that this is one of the few exceptions to the “SOQL is case insensitive” rule. You’ll notice within the name field that while there are entries for both “Pavlova” and “pavlova,” they are not treated as duplicates, because they are not exactly the same, one has a capital “P and the other a lower case “p.”

MIN(), MAX(), SUM() and AVG()

While keywords MIN(), MAX(), SUM(), and AVG()are pretty self-explanatory, I think that there are a few  cool not-so-obvious ways to use them that are worth a mention.

As you can imagine, MIN() finds the lowest value, and MAX() the highest value – but this can be used not only with numbers, but also with dates (finding the earliest/latest date), as well as with text fields and pick lists (sorting and then finding the first/last values).  Neat right?

SUM() and AVG() can only be used with numeric fields, and as you can probably guess, respectively add field values together and return their average value.

All of these functions require that you feed them a particular field, for example:

SELECT SUM(cookingTime__C) FROM recipe__c

Results in the following…9.7 hours-worth of cooking pleasure!

 

Aggregate Functions and GROUP BY

If aggregate functions are cool, then using them with the GROUP BY key phrase is, like, off the chart RAD!

What GROUP BY allows us to do is to calculate aggregates that are grouped by particular values. Again, this is what we do when we create a summary report, and using the Group Drop Zone.

For example, maybe we wanted to count all our recipes and see how many we have in the various categories. Below is the list of recipes and their categories:

We could use the following SOQL query to summarize counts by the recipe category:

SELECT category__c, COUNT(name)
FROM recipe__c GROUP BY category__c

Returns the following:

We can even use aggregates with filters using WHERE. Perhaps we would like to count, by category, how many recipes we had that were quick to make (i.e., take under an hour to make).  Imagine below are the recipes we have in our custom object:

We would use the following SOQL statement to pull this data:

SELECT Category__c, count(name)  FROM recipe__c
WHERE totalTime__c < 1
GROUP by Category__c

With the following result:

As you can see, we only have one dessert and one baking recipe that are quick to make. Seems like we need to add some more!  Hmmm…I think there is the subject for another post in here…can someone say DML?

Aggregate Functions and HAVING

There’s another way to filter SOQL aggregates, which allows us to calculate aggregates for records that fall within a certain criteria. For this, we can use the HAVING keyword.

While WHERE filters records before they are aggregated, HAVING filters aggregates after they have been aggregated.

Let’s see it in action. Imagine we were only looking for recipe categories that were used by more than one recipe:

SELECT Category__c, count(name) FROM recipe__c
GROUP by Category__c
HAVING count(name) > 1

In plain English, this is saying, “group and count my recipes by category, and then show me only those categories that have more than one recipe.”

If you compare this with the earlier screenshot, you’ll see that the “Main Course (Beef)” is missing; that’s because it contains only one recipe and is filtered out of our results set using the HAVING keyword.

If you’re a proud #DataNerds like me this is a lot of fun…and it’s only the beginning! Let’s move on to working with dates and dates/times.

Working with Dates and Date/Times

Dates and date/times are always a little tricky to work with, because they can be entered in so many ways. Take the date that I’m writing this post (4/25/15 at 8:40AM Pacific Time). I could write that any number of ways…I’ve listed just a few…and not all of these are acceptable formats to feed SOQL.

If we were just working with the date, April 12, 2015 could be written as:

• 4/25/15
• 04/25/2015
• 2015-04-25 <–This is the only date format acceptable to SOQL
• April 25, 2015
• 04/25/2015 00:00:00

If we were dealing with both the date and the time, then April 12, 2015 at 8:40AM Pacific Time could be written as:

• 4/25/15 8:40 AM
• 4/25/2015 08:40
• 04/25/2015 08:40:15
• 2015-04-25T08:40:15-08:00 <–This is one of a few date/time formats acceptable to SOQL.

So how are we going to deal with all those options? So-o-o glad you asked!

You have two options when dealing with dates and times in SOQL: either pass a specific date, or use a date placeholder (e.g., TODAY or TOMORROW). Let’s look at how you would use both!

Option One: Pass a specific date

Using this option, we are directly specifying a particular date or date/time. SOQL is a pretty darn picky eater, so in order to use this option, you must feed (aka format) it Date/DateTime values in very specific ways.

Field Format	Acceptable Format(s)	Example
Date	YYYY-MM-DD	2015-04-15
DateTime	Two options with a positive or negative TimeZone offset from Coordinated Universal Time (UTC): YYYY-MM-DDThh:mm:ss+hh:mm YYYY-MM-DDThh:mm:ss-hh:mm   One option with the date shown actually in the UTC Zone: YYYY-MM-DDThh:mm:ssZ Notes: DateTimes are stored as UTC offsets within Salesforce Transitions between the date section and the time section is marked with “T.” “Z” is used instead of a TimeZone offset if the dateTime is within the actual UTC Zone. Random fact – this is also known as “Zulu” time.	2015-04-15T08:40:15-08:00 2015-04-15T11:40:15-05:00 2015-04-16T3:40:15+12:00 2015-04-15T16:40:15Z All the above represent the same date/time, just with different TimeZone offsets. Click to enlarge The first is Pacific Standard Time, the second is Eastern Standard Time, the third is New Zealand Standard time (Go Kiwis!), and the last is Zulu time. Check out this fabulous World Clock TimeZone Converter. You gotta love the Internet; there is a tool for everything!

But what does this actually look like when we use it in SOQL? The following code is an example of how to we can use a date/time in SOQL:

SELECT name, id, createdDate FROM recipe__c
WHERE createdDate > 2015-01-01T00:00:00-08:00

This query would result in the following result set:

If you wanted to look at records that were created between a particular date range, you can use a combination of comparison operators, for example:

SELECT name, id, createdDate FROM recipe__c
WHERE createdDate > 2015-01-01T00:00:00-08:00
  AND createdDate < 2015-01-31T00:00:00-08:00

This will look for any recipe that was created between 1/1/15 and 1/31/15; but not records actually created on either of these dates. We are specifically looking here at records created after 1/1/15 and before 1/31/15. If you wanted to include both dates, then there is a quick fix for that; just add an equals sign after to the greater and lesser sign. This literally means “greater than or equal to” (e.g., >=) and “Less than or equal to” (e.g., <=).

SELECT name, id, createdDate FROM recipe__c
WHERE createdDate >= 2015-01-01T00:00:00-08:00
  AND createdDate <= 2015-01-31T00:00:00-08:00

Option Two: Pass in a “Date Literal”

The second way we can use dates is much easier (and more dynamic), because it doesn’t require us to enter a particular date, but instead uses a placeholder for date ranges. These are called “Date Literals,” and include options like YESTERDAY, TODAY, TOMORROW, LAST_WEEK, etc.

We have this same function available to us when we create reports in Report Builder, and choose one of the standard date ranges.

The great thing about this is that instead of having to update our query with a new specific date range, we can feed it a placeholder which is always going to be relative to today. For example, say you want to run a query that pulls out all the recipe’s that were created THIS_MONTH? If we used date option one (specific values), we’d need to update the dates in the query each month. Not so with Date Literals. You just use the placeholder of THIS_MONTH, and you never have to change it again. No matter what date it was, it would always pull out the entries created in whatever month you were in!  Wa-a-a-ay cool!

Let’s check out how super easy Date Literals are to use! The following query is pulling out any recipes that were created in the calendar month before the current one (i.e., when I wrote this post, it was April 2015, so LAST_MONTH would be March 2015):

SELECT name, id, createdDate FROM recipe__c
WHERE createdDate = LAST_MONTH

Boo-yah! So easy!!

I can also use the last n days (where “n” is a number). For example, if I wanted to pull out all the recipes that have been created in the last 100 days, it would look like this:

SELECT name, id, createdDate FROM recipe__c
WHERE createdDate = LAST_N_DAYS:100

I’ve talked about some of the options for Date Literals, but there are a ton more. Definitely check out your options at the SOQL Date Formats and Date Literals Documentation on Salesforce.

That’s enough about working with dates, let’s move on to some other cool stuff, like…well…LIKE!

How much do I LIKE SOQL? (…or I’m wild about Wildcards!)

I love using comparison operators. Could be that I’m a little nutty (nah!), but I suspect, deep down you are with me on this. Operators are cool, because we use them in WHERE clauses to help us find just the data we’re looking for. Super awesome!

We’ve used some in this and previous posts. For example, we know about using =, != (including how to stamp your foot when you use it!), <, >, and even <= and >=.

But what is this “LIKE” operator? What the heck is that used for?

LIKE is a very special operator, because it can be used, along with wildcards, when we don’t quite know what we’re looking for in a text string.

Remember back to our first SOQL post, where we looked at finding all the contacts whose last name is “Smith”, but we also wanted to include all those whose last name was “Smyth”? That query looked like this:

WHERE lastName = 'Smith' OR lastName = 'Smyth'

We said another way to write this query was using the IN keyword:

WHERE lastName IN ('Smith','Smyth')

We also said that there was yet another way to query Salesforce and look for the same records…well congratulations, because you’ve made it to “that time” where I reveal the super-secret-ey secrets of SOQL. Check this out!

WHERE lastName LIKE 'Sm_th'

Wow! Really? This does the same thing? Yep, the “_” inside the single quotes basically says, “replace me with any single character.” So it would find “smyth”, “smith”, and also “Smuth”, “Smeth”, and even “smzth.”

Let’s see some other use cases that are super awesome.

What if you wanted to find all the Contacts whose first name starts with the letter “S”? Super simple!

SELECT name, id FROM CONTACT WHERE name LIKE 'S%'

Will result in the following data set:

Way cool! The “%” wildcard stands in for zero, one or more characters, so depending on where you put it, it can help you search for different things. For example:

SELECT name, id FROM CONTACT where name like '%S'

Will find any contact whose name ends in “s.”

And even cooler…

SELECT name, id FROM CONTACT where name like '%S%'

Will find any contact whose name contains an “s.”

Pretty powerful stuff right?

I hate to tell you this, but we’re almost at the end of our intro to SOQL. I know…sad right? But before we do one final wrap up, I made a promise in my first post that I’d talk about how SOQL differs from its older sister SQL (Structured Query Language)…so here we go.

The difference between sassy SOQL and sophisticated SQL?

For those of you who already know SQL will find the SOQL pretty darn easy, but there are a few things that might trip you up. Below is a list of the most common “Gotchas”:

• Unlike in SQL, you can’t modify data with SOQL, only retrieve it. In Force.com coding, we use DML to programmatically modify data. We talked a little about this in my sObject post, but I’ll cover that more in a later post.
• In SOQL, we use dot notation directly in the select statement to pull data from related objects, rather than creating official Join statement as we would in SQL.
• In SOQL, WHERE clauses searches that use the LIKE keyword are not case sensitive (we mentioned that above).

There are some other, more subtle differences, but for the most part, SOQL and SQL are pretty alike.

Wrapping Up

Phew! Congratulations! You’ve made it through three posts on SOQL and you lived to tell the tale.

By now, you will have all you need to get started with SOQL (and then some!). This isn’t everything that we could talk about, but this is definitely enough for you to get into some your SOQL freak on.

You can probably tell that I really love SOQL and hope that you’ve gotten some helpful tips and tricks out of this series.

Let’s do one final wrap up of what we’ve learned about SOQL:

• SOQL is used to pull data out of Salesforce Objects (also known as “querying the database”).
• Like Apex, for the most part, SOQL is not case sensitive.
• There are only two required keywords for every SOQL statement:
  • SELECT – Which fields do you want to see?
  • FROM – What object are you pulling from?
• There are a number of optional keywords, including:
  • LIMIT – How many records do you want to pull?
  • ORDER BY – What sort order do you want? ASCending or DESCending? If you don’t specify an order, then SOQL will assume ascending.
  • WHERE – How do you want to filter the results?
• Within WHERE clauses, we can use a number of operators, including:
  • Equals (“=”) and Not Equal To (“!=”)
  • Greater Than (“>“) and Greater Than or Equal To (“>=“)
  • Less Than (“<“) and Less Than or Equal To (“<=“)
  • LIKE – Used to filter for text strings, and can be used with the “%” and “_” wildcards.
  • IN – Used to filter by a comma separated list of values
  • AND – Used to combine multiple filters; both sides of the AND must be true
  • OR – Used to combine multiple filters; only one side of the OR needs to be true
• We can select to query fields from a single object, and any objects that are related to it (for up to 20 relationships per query, and up to 5 levels within a single relationship):
  • We use Dot Notation to move from a child up to a related parent object.
  • We use Subqueries to move from a parent down to a child relationship.
• You can create rollup-like aggregate queries that:
  • Include SUM, AVG, MIN, MAX, COUNT and COUNT_DISTINCT
  • Use GROUP BY to create “summary report”-like data results
  • Use WHERE to filter records before they are aggregated
  • Use HAVING filters aggregates after they have been aggregated.
• We can filter by Date/DateTime fields using either:
  • Exact dates (e.g., 2015-04-15 and 2015-04-15T08:40:15-08:00)
  • Date Literals (e.g., TODAY, THIS_WEEK, NEXT_MONTH)

That is massive! Nice job you!

Until next time…see you in the cloud coding kitchen!