Skip to content

Instantly share code, notes, and snippets.

@jongpie

jongpie/email.md

Created April 6, 2022 16:41
Show Gist options
  • Save jongpie/1abf0c5d5b0d3779ff78de314faae46e to your computer and use it in GitHub Desktop.
Save jongpie/1abf0c5d5b0d3779ff78de314faae46e to your computer and use it in GitHub Desktop.
Download ZIP
That one time in 2015 that I shared my thoughts on SOQL vs SQL with James Simone
Raw
email.md

Hey James,

I just realized we haven’t discussed how to query SF data in any of our SF/Skuid trainings, and you might want to do so while I’m out this week (or you may just be curious about how it works). We can discuss this more in person if you want, but there are a couple of things to know about querying in Salesforce (spoiler alert: prepare to be sad).

Overview

  • Salesforce’s custom implementation of SQL is called SOQL – Salesforce Object Query Language.

  • It takes everything you love about SQL, throws 75% of it out the window and then burns the rest, leaving you with the sad, charred remnants of a once beautiful thing.

    • There are several tools you can use to query Salesforce – they’re pretty much all terrible, both because SOQL is terrible and because the tools are typically missing standard features that I would expect a querying tool to have

  • We do have some of the Salesforce objects synced to SQL server, but they don’t have all fields and are only updated once/day. If that works for your purposes, I’d say avoid SOQL for now if you can.

Big changes from SQL to SOQL

  • Want to do ‘select * from account’? Too bad, there are no wildcards in SOQL because screw you, Salesforce does what they want. You must specify each column name in your query.

  • This applies everywhere, including aggregates

    • SQL: SELECT Type, count(*) FROM Account GROUP BY Type
    • SOQL: SELECT Type, count(Id) FROM Account GROUP BY Type

  • Want to do a join in SOQL? Great! But Salesforce said "we COULD follow standard joining syntax from SQL, but why? Why not make something different and confuse & anger everyone? That’d be pretty good, right?" So there are 2 types of sort-of joins in SOQL

    • Child record to parent record join: for example, you have an opportunity and you want to query the name of the account on the opportunity. Here’s how you’d do it in SQL vs SOQL:

      • SQL: SELECT o.Id, o.AccountId, a.Name FROM Opportunity o join Account a on o.AccountId = a.Id
      • SOQL: SELECT Id, AccountId, Account.Name FROM Opportunity

      Breaking it down a bit, ‘Id’ is the Salesforce ID of the opportunity. ‘AccountId’ is the Salesforce ID of the account. ‘Account.Name’ is the weird bastardized way of joining to accounts to get the account name. This works with any master-detail or lookup field (the 2 types of relationship fields that we discussed last week)

    • Parent record to children record join: want to get an account and all of its opportunities?

      • SQL: SELECT a.Id, a.Name, o.Id, o.Name, o.StageName FROM Account a LEFT JOIN Opportunity o on a.Id = o.AccountId
      • SOQL: SELECT Id, Name, (SELECT Id, Name, StageName FROM Opportunity) FROM Account

      Breaking this one down, the first 2 columns (Id, Name) are pulling from the account object. The weird ‘sub-select on opportunity that’s shoved in with the columns from the account object’ is how you query the children records (in this case, opportunities) for an account – it will give you the opportunity’s ID, name & stage.

  • Checking for null values

    • SQL: where some-field is not null

    • SOQL: where some-field != null

  • Case statements: they don’t exist in SOQL :-(

  • Column aliases: they only work in aggregate queries and even then there are sometimes issue – it’s unfortunately best to just avoid them

  • Always use single quotes for any where conditions, never double quotes

  • Date comparisons: this actually a nice feature. SOQL has several options built in for comparing dates (more details here)

    • Get all accounts created since last week: SELECT Id, Name FROM Account WHERE CreatedDate >= LAST_WEEK

    • Get all leads that were modified today: SELECT Id, Name FROM Lead WHERE LastModifiedDate = TODAY

The easiest option to get started with is called Salesforce Workbench – it’s a web-based tool that helps with several aspects of working with Salesforce, including querying.

- Open this link https://workbench.developerforce.com/login.php

- Select either production or sandbox as the environment (depending on where you want to query)

- API version: select 32.0

- Click ‘Login with Salesforce’ – it may prompt you to log into Salesforce & give permission to workbench – click ‘allow’ if needed

- Next page, select:

  • Jump To: SOQL Query

  • Object: Whatever object you want to query

    • The SOQL Query page will load – there’s a box called Fields that makes it easy to select the fields you want, and filter options on the right side of the page. So this will help walk you through the process of building out the query & make sure the syntax is correct

This is by no means everything there is to know about SOQL, but should give you a start. More details about SOQL can be found at these 2 links:

- http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

- https://developer.salesforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

Hope I’m not overwhelming you with all of the long emails I’ve been sending you lately!

Thanks, Jonathan Gillespie

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment