It is clear the community of developers and admins is growing at an alarming rate, so much that we are now starting to see books which are focused on specific areas of the platform, for example “Visualforce” and “Chatter” and now this one covering the topic of SOQL.
SOQL is Salesforce’s answer to SQL. Other than almost identical acronyms, and the cursory “SELECT field FROM table”, the Salesforce version is an entirely different animal.
New users to the platform will be frustrated by the many holes, such as you cannot simply compare one column to another, or perform a query joining two unrelated objects. That’s right you heard me correct – like I said, completely different animal. It’s akin to the experience Java developers go through when they cross over to Apex; “you told me it’s 80% Java, but what with all these limits”.
The book is broken down into the following chapters:
- Limitations and Best Practices
The introduction covers off the core use cases of SOQL and the general syntax and structure:
Select fieldNames from Object where Condition
It also describes nicely the importance of understanding API names and how to find them out. This will help educate the less technical admins or business users who want to run some complex queries on how they go about getting the API field names and object names for their queries. This is where you realise the importance of defining meaningful API names.
The Basics then moves beyond the simple select queries and into “Alias” notion, although I rarely see anyone doing this in the workplace (correct me if I’m wrong about that). The main content of this chapter drills into the scope of what is possible after the WHERE clause. Most of these will seem natural to someone with knowledge of SQL, as expected Logical Operators and Comparisons are all present and offer all the basic functionality to filter your data set down to just the records you want. Some nice example are given for more advanced WHERE clauses in the form of IN and NOT IN.
The Advanced chapter starts to cover off how to query data from multiple objects, which is where SOQL is very different from its legacy SQL cousin, there are no INNER or OUTER joins, instead you must query related records using nested sub queries inside the same query like this:
Select Id, Name, (Select FirstName, LastName from Contact) from Account
To a SQL veteran this may seem strange, but in the SOQL world this forms a perfect representation of how data is structured in the UI transposed as a query string.
Next it moves onto inbuilt functions: if you exist in a world with different languages and the translation workbench, then the nifty function “toLabel” proves useful and allows you to easily retrieve values in a user’s locale.
SELECT Name, toLabel(Industry) FROM Account
Common aggregation functions fall nicely in here; these both look and work very similar to SQL functions. Group By is the key component here and can be used with functions such as COUNT, SUM, MAX & MIN plus COUNT_DISTINCT, something which I missed in my own learnings. Rounding off the chapter is the HAVING clause which provides the ability to filter results which are aggregated.
The penultimate chapter whistles through Limitations & Best Practices, starting with how to LIMIT result sets. As someone who reviews code on a daily basis I hate seeing SOQL statements which are unrestricted, so this point is well made. If the result set is less than 50k, then please put LIMIT 50000.
Last chapter calls out some of the most used tools which can be used for performing SOQL against your Salesforce Org, the main tools are:
- Force.com Explorer – Air based App
- Workbench – My personal favourite tool for quick access to APIs and running SOQL; it also provides the ability to quickly dump out the result set in bulk CSV or XML. It’s by far the quickest tool to access, but open to be challenged on that!
- Dataloader.io – I have not used this one, but we commonly see it mentioned on the success community as a strong alternative to Salesforce Dataloader (need to add to the list as something to check out and find out what I have been missing.)
- The Apex Dataloader – the original Dataloader tool provided by Salesforce. Most people have a love hate relationship with it — it works, it doesn’t work…
- One not included in the book, is the Dev Console inside Salesforce which allows you to enter SOQL queries.
Knowledge is king, and knowledgeable you will be on SOQL after reading this book. It is short and direct, whereas Salesforce documentation is often vast and wide. This book looks to consolidate all the key information and practices into a short book using very simple language and examples.
The book is well suited to the more savvy business user who understands how to execute a few SQL queries and now wants access the raw data. It’s also light reading for new developers and admins looking to master Apex by breaking out an entire topic on its own.
Covering the book “Getting Started with SOQL”
About the author: Magulan D is a Salesforce.com administrator and developer. He started his career as a PHP developer and also worked as a Siebel CRM developer. During his career as a PHP developer, he created many sites. Please check out his blog http://www.infallibletechie.com/