SOQL – How I Query With Thee, Let Me Count the Ways
February 22nd, 2010
I’ve been wanting to write this post since the new aggregate functions were announced in Spring 10. With the new 18 version of the API in sandboxes I can finally do a high level overview of SOQL (Salesforce Object Query Language) for all of the newcomers to the Force.com platform. This is definitely not an all-encompassing look as SOQL but something to whet your appetite with examples and screenshots. The Salesforce SOQL docs have a lot of great detailed info, but one of the things that I think is missing is what the results of the SOQL queries look like. This article does not cover Dynamic SOQL.
Most developers are familiar with SQL (Structured Query Language) so SOQL isn’t much of a stretch. There are some really cool features of SOQL that you will find extremely productive but there are some restrictions that will make you want to pull your hair out. However, as with everything on the Force.com platform, SOQL is evolving and becoming better and better overtime. From a high level, there are a few important differences in the two languages:
With SQL you can retrieve and modify datasets directly. However, with SOQL you can only retrieve datasets. You can then use DML statements with these records to performs inserts, updates, deletes and upserts (creates new records and updates existing records within a single statement).In SQL joins are second-nature to retrieve data from multiple tables. However, with SOQL, one of my favorite features is its support for dot notation to join related objects implicitly. We’ll look at both child-to-parent and parent-to-child relationship shortly.The biggest thing to remember is that SOQL is not SQL. SOQL does not support full SQL syntax so you’ll have to rethink the way you query for data. With Spring 10 Saleforce.com has added some new aggregate functions (finally!!) and SOQL functions but there will be many times when you’ll have to break open the docs to see what you can and can not do with SOQL.
- With SQL you can retrieve and modify datasets directly. However, with SOQL you can only retrieve datasets. You can then use DML statements with these records to performs inserts, updates, deletes and upserts (creates new records and updates existing records within a single statement).
- In SQL joins are second-nature to retrieve data from multiple tables. However, with SOQL, one of my favorite features is its support for dot notation to join related objects implicitly. We’ll look at both child-to-parent and parent-to-child relationship shortly.
- The biggest thing to remember is that SOQL is not SQL. SOQL does not support full SQL syntax so you’ll have to rethink the way you query for data. With Spring 10 Saleforce.com has added some new aggregate functions (finally!!) and SOQL functions but there will be many times when you’ll have to break open the docs to see what you can and can not do with SOQL.
Relationship Queries
Relationships are arguably the most powerful feature of SOQL. They allow you to perform joins on multiple objects and traverse the relationship chain to find and return objects. Relationships are available for standard and custom objects in Salesforce. However, their syntax is slightly different. The classic relationship example is accounts and contacts. An account (parent) can have multiple contacts (children).
Child-to-Parent Relationship
This type of query returns child objects (contact) containing parent (account) information:
For child-to-parent relationships with custom objects the syntax is slightly different but the results are essentially the same. In the query below there is a custom object (Affiliate__c) with a lookup relationship on the Account object. Notice in the query below we use the “__r” instead of the object’s name since this is the name of the relationship. This will be explained shortly in the parent-to-child section below.
For each relationship you can specify up to 5 levels. This makes for some really cool queries that let you reach way up the relationship chain to fetch data. For instance you can write a query like this which spans only 4 levels.
Parent-to-Child Relationships
These types of queries are almost always one-to-many. You specify the relationship using a subquery, where the initial member of the FROM clause is the subquery is related to the initial member of the outer query FROM clause. Here’s where the relationship comes into affect. With standard objects you use the plural name of the object in the subquery. Here’s how you would find the name of the relationship using Eclipse:
The following query returns the name for each account and then for each account another collection of contacts containing their first name, last name and email address.
For custom objects the relationships are slightly different. When you create a relationship between two objects, the Force.com platform generates a relationship name for you. So if your relationship field name is “Foo__c” the relationship name would be “Foo__r”. I’ve seen some really strange relationship names generated so you can change the name (here are instructions how) if you need to. So a sample query might look like:
You can get really crazy with relationships and where clauses in the subqueries and run something like the following.
Joins
SOQL also support semi-joins and anti-joins. Some examples are:
Semi-Joins with IN query
ID field Semi-Join
ID field Anti-Join
Multiple Semi-Joins or Anti-Joins
Aggregate Functions
Aggregate Functions are the long-awaited additions to SOQL for Spring 10. Not all fields are supported so check out this list for details.
COUNT() returns the number of rows that match the filtering conditions and COUNT() must be the only element in the select list. The resulting query result size field returns the number of rows and the records will returns null.
COUNT(fieldname) returns the number of rows that match the filtering conditions and have a non-null value. An AggregateResult object in the records field contains the number of rows. Do not use the size field for the resulting records.
COUNT_DISTINCT() returns the number of distinct non-null field values matching your query criteria.
SUM() returns the total sum of a numeric field based up your query criteria.
AVG() returns the average value of a numeric field based up your query criteria.
MIN() returns the minimum value for a field.
MAX() returns the maximum value for a field.
GROUP BY
As in SQL you can use GROUP BY to summarize and roll up your query results instead of processing records individually. There are a number of options, restrictions and conditions for group by, so check out the docs for more info.
You can also calculate subtotals for aggregate data in query results by using GROUP BY ROLLUP.
HAVING
Using HAVING you can filter the results returned by aggregate functions where you might normally want to use a WHERE clause. For instance, this query will fail:
Date Functions
The Force.com platform provides you with a number of date functions that can be used in SOQL to make your life easier. Check out the docs for a complete list.
Categories: Code Sample, Salesforce
Best post I’ve seen in a long time. Great job Jeff!
Thanks Andrew! That’s very nice of you to say.
I second Andrew-
very great post Jeff. Should be mandatory for everyone who is new to Apex coding.
Fantastic detail! Even as an employee I only learned some of the child–>parent syntax recently. I think this overview is even better than Help & Training.
Great stuff. The “missing documentation” on SOQL. Salesforce should hire you to do the cheat sheets on SOQL.
What tool are you using to run these queries?
Thanks for the nice word Mike! I actually used Simon Fell’s SoqlXplorer for the Mac. You can’t build queries as well as you can in Eclipse BUT you can copy query results and display related data a little easier. I talked with the Eclipse PM and copying search results just missed the last release so I’m betting it will be in this upcoming one.
I can’t believe there are no Windows based tools that can take advantage of the 18.0 API.
(
I guess I’ll have to wait until the new Eclipse add-in is out
Indeed, its a great one. I have started my own to help others like you Jeff. Keep inspiring people.
http://salesforcetrekbin.blogspot.com/2010/04/group-by-clause-in-salesforce-soql.html
Hi jeff,
You probably are doing the best job by posting all the things that could be done using salesforce. I have one question i am using flex as UI in salesforce application. How can i get distinct values for a particular column. One more thing how can i implement dependant picklist? we need to have custom code for that right.
If you use the 18 API, you can probably use the group by function to return distinct values. Check out this post for more info. I’ve got some code for dependent picklists in Flex so I’ll try to post that shortly.
Great post Jeff,
Here is one I can’t live without (Leads by hour)
select DAY_IN_MONTH(convertTimezone(createddate)) Day, hour_in_day(convertTimezone(createddate)) hour, count(id) mycount
from lead where createddate = today
group by DAY_IN_MONTH(convertTimezone(createddate)), hour_in_day(convertTimezone(createddate))
order by DAY_IN_MONTH(convertTimezone(createddate)), hour_in_day(convertTimezone(createddate))
Dude, you know how long I’ve been waiting for this? I have an object with over 80,000,000 records and I can’t do a record count grouped by certain fields with reports; it just pukes. Now I’m going to write some code to update a summary object and populate it with stats and stuff for analytics. Hey you have a great site. Come work for me.
Why don’t YOU come work for Appirio!
What tool are you using to execute the SOQL queries in the screenshot!?
I used SoqlXplorer (http://www.pocketsoap.com/osx/soqlx/) for the query examples. It’s only available for the Mac.
Is there any way i can query in soql where i can retrieve records from 10- 20 . I need to do this because i am using standardsetcontroller for implementing pagination and when i use querylocator i can get maximum of 10000 records and i have much more records in database.
This is the third time that I have refered it. Awesome post!
Thanks @Osama!!
Hi Jeff,
Thanks for a great post. I’m doing a subquery, and trying to limit the outer query to rows that have at least one value for the subquery, like this:
[SELECT firstname, lastname, (SELECT status, campaignid FROM CampaignMembers WHERE ) WHERE ]
Without an outer where clause, I get all Contacts returned, but most have no data on the subquery. What kind of WHERE clause will give me just the people with related CampaignMembers?
Thanks
What SOQL editor you are using? It look’s nice.
@Arpit, It’s SoqlXplorer. You can download it at pocketsoap.com.
“In SQL joins are second-nature to retrieve data from multiple tables…The biggest thing to remember is that SOQL is not SQL. SOQL does not support full SQL syntax so you’ll have to rethink the way you query for data”
Can you expand on this for multiple (i.e. more than 2) tables?
@Oscil8, sure! You can join the account and contact tables: select contact.id, contact.email, contact.account.name from contact or select account.name, (select contact.firstname, contact.lastname from account.contacts) from account.
“Using HAVING you can filter the results returned by aggregate functions where you might normally want to use a WHERE clause. For instance, this query will fail:”
I think this query can also be executed using WHERE clause:
select billingstate, count(name) from account where billingstate in (‘CA’,'TX’) group by billingstate
Hi,
What if I want to use a field of a previous query in another query?? Any help?
Example:
WebService static void CompruebaEstadoRFB(String NombreRFB)
{
RFB_Capacidad__c[] RFB=[Select id, CRM_Id__c, Estado_de_Facturacion__c from RFB_Capacidad__c
where id= :NombreRFB];
Elemento_a_Facturar_Capacidad__c[] EF=[Select id, CRM_Id__c, Pendiente_de_Revisi_n_Local__c,
Estado_de_Factura__c, RFB_Capacidad__c
from Elemento_a_Facturar_Capacidad__c where CRM_Id__c= RFB.CRM_Id__c];
//This previous statement is not working…
For (RFB_Capacidad__c Dato:RFB)
{
If (Dato.Id == NombreRFB)
{
If (Dato.Estado_de_Facturacion__c == ‘Facturable’)
{
For (Elemento_a_Facturar_Capacidad__c DatoEF:EF)
{
If (DatoEF.CRM_Id__c == RFB.CRM_Id__c)
{
If (DatoEF.Pendiente_de_Revisi_n_Local__c == ‘Revisado’)
{
DatoEF.Estado_de_Factura__c = ‘Facturado’;
update DatoEF;
}
}
}
}
}
}
}
Thanks for the post… been coming back to reference. Any constraints on using any of these joins via the dataloader? I have been able to successfully get through the extraction wizard in some cases however the soft file often has no data in the expected column
Hi Jeff,
I’d like to return a list of accounts with a count of leads that ocurr in a given time period. Is this possible in SOQL or do I need to import everything into a SQL database and do the counting and aggregating there. Thanks much.
Cheers
Joanthan
I might be missing something here or lost touch with the SOQL.
I just want the Parent with count of child record returned.
I cannot apply count on sub query. Is there a query based solution or I need to write within APEX to count it.
Select c.Type, c.Status, c.Name, c.IsActive, c.Description, (Select CampaignId From CampaignMembers) From Campaign
~NK
Great post. Any chance you can point out how to query a product and return multiple price book prices in one query?
Thanks!
Something like this…
select Id (select UnitPrice from PricebookEntry where Pricebook2ID = ’01s30000000FVgGAAW’) (select UnitPrice from PricebookEntry where Pricebook2ID = ’01s30000000MZw3AAG’) from Product2
Of course I have something wrong..
I think I figured it out…
select id,name,(select id, Pricebook2.name, unitprice from pricebookentries ) from product2 where Product2.Family =’Surfboards’ and id in (select product2id from pricebookentry )
This is fantastic. I love PocketSoaps apps (most notably Lexiloader – his version of Data Loader). Here is my big question and I can’t figure it out – how do I join two custom tables that are connected by a lookup? Most of what is highlighted here is all about Parent/Child.
Thoughts?
It seems that even a basic lookup creates a parent/child dynamic – i was thinking of it solely in the parent/child object relationship sense.
So, forgive me a second question, but how would you join three tables? e.g. zip to zip_state to state (not the best example…) I can write a join for zip to zip_state and another from state to zip_state…
@Yoni, it should be in there. Search the page for “Foo__c”.
Hi..
I want to get counts of records for each and every objects. Please suggest me the best way to get it. I am new to Salesforce.
@Yoni, not sure about our relationships but SOQL queries can join object up to five levels deep. Check the salesforce.com Help for more info.