Finally, The Queries
I have been quietly writing SOQL queries in examples since about episode 13 and saying “don’t worry, we’ll cover this later.” Well, it’s later.
Two query languages to get through:
SOQL (Salesforce Object Query Language) – gets records from specific objects you name. This is the one you’ll use constantly.
SOSL (Salesforce Object Search Language) – searches text across multiple objects at once. Much rarer, but the right tool sometimes.
Both look a bit like SQL. Neither is SQL. Most importantly, there are no joins, which is the single biggest adjustment if you’re coming from a relational database background.
Your First Query
List<Account> accounts = [SELECT Id, Name, Type FROM Account];Those square brackets are Apex saying “this is a query.” The database is built right into the language, which is genuinely one of the nicest things about Apex, as I mentioned way back in episode 3. No connection strings, no drivers, and the compiler checks your field names for you.
Two rules that catch everyone:
1. There is no SELECT *. You must name every field you want. Annoying at first, genuinely good for you, since it stops you dragging back 300 fields when you needed two.
2. If you didn’t query it, you can’t use it. Reference a field you didn’t select and you get System.SObjectException: SObject row was retrieved via SOQL without querying the requested field. Which is a mouthful, but at least it tells you exactly what’s wrong.
Filtering With Bind Variables
Set<String> accountTypes = new Set<String>{'Customer - Direct', 'Customer - Channel'};
List<Account> acctList = [SELECT Id, Name, Type
FROM Account
WHERE Type IN :accountTypes];That colon in :accountTypes is a bind variable, and it’s how you drop an Apex variable into a query. You can bind a single value, a Set, or a List.
Binding a collection with IN is the pattern that makes bulkification work. One query, however many Ids.
And remember the Map constructor trick from episode 19, which works directly on a query:
Map<Id, Account> acctMap = new Map<Id, Account>(
[SELECT Id, Name, Type FROM Account WHERE Type IN :accountTypes]
);The Rest Of The Clauses
SELECT Id, Name, AnnualRevenue
FROM Account
WHERE AnnualRevenue > 5000
AND Type = 'Customer - Direct'
AND Name LIKE 'Acme%'
ORDER BY AnnualRevenue DESC NULLS LAST
LIMIT 100LIKE with % as the wildcard does partial matching. ORDER BY sorts, and NULLS LAST is worth knowing because Salesforce puts nulls first by default, which is almost never what you want.LIMIT caps the results. Use it while you’re experimenting so you don’t accidentally pull 50,000 records into memory.
Relationships, Or “Where Did The Joins Go?”
There are no joins. Instead you traverse relationships, in two directions.
Going up (child to parent) uses dot notation:
List<Contact> contacts = [SELECT Id, LastName, Account.Name, Account.Industry
FROM Contact];
System.debug(contacts[0].Account.Name);One query gets you Contacts and their Account details. No second query, no map, no loop. You can go up five levels this way.
On custom relationships, replace the __c on the field with __r: My_Custom_Object__r.Name. Everybody forgets this at least twice.
Going down (parent to child) uses a subquery:
List<Account> accounts = [SELECT Id, Name,
(SELECT Id, LastName FROM Contacts)
FROM Account];
for (Account acct : accounts)
{
for (Contact cont : acct.Contacts)
{
System.debug(acct.Name + ' - ' + cont.LastName);
}
}Note it’s Contacts plural, the relationship name, not the object name. For custom objects it’s usually your object’s plural label with __r on the end.
These two are the single best way to keep your query count down. Before you write a second query, ask whether a relationship would have got you there in the first one.
Aggregates
List<AggregateResult> results = [SELECT Type, COUNT(Id) total, SUM(AnnualRevenue) revenue
FROM Account
GROUP BY Type];
for (AggregateResult ar : results)
{
System.debug(ar.get('Type') + ': ' + ar.get('total'));
}COUNT(), SUM(), AVG(), MIN(), MAX() all work with GROUP BY. Results come back as AggregateResult objects and you pull values out with get() using the alias you gave the column.
Handy detail: aggregate queries don’t count against your heap size the way pulling back all the records would, so if all you need is a total, this is far cheaper than querying everything and counting it yourself.
Dynamic SOQL (Careful With This One)
Set<String> accountTypes = new Set<String>{'Customer - Direct', 'Customer - Channel'};
String queryString = 'SELECT Id, Name FROM Account WHERE Type IN :accountTypes';
List<Account> acctList = Database.query(queryString);Building a query as a String lets you decide fields or filters at runtime. Genuinely useful for configurable components.
But here’s the warning, and it’s a real one: the compiler can no longer check your query. Typo a field name and you find out in production instead of at save time.
Worse, if you concatenate user input into that String, you’ve created a SOQL injection vulnerability. If you must build a query from user input, use String.escapeSingleQuotes() on it. Better still, use bind variables like the example above, which are safe by design.
Use static queries unless you genuinely can’t.
The Limits You Need To Respect
100 SOQL queries per synchronous transaction (200 async).
50,000 records total returned per transaction.
The first you already know how to avoid from episode 30: never query in a loop.
The second is why the SOQL for loop exists, since it chunks records in batches of 200 instead of loading everything at once.
You can check where you stand at any point:
System.debug('Queries used ::: ' + Limits.getQueries());
System.debug('CPU time ::: ' + Limits.getCpuTime());Sprinkling Limits.getCpuTime() around is a genuinely good way to find out which bit of your code is actually slow, rather than guessing.
Now, SOSL
SOSL searches text across several objects at once. Where SOQL asks “give me Accounts matching this,” SOSL asks “find this word anywhere.”
List<List<SObject>> soslResults = [FIND 'Taco' IN ALL FIELDS
RETURNING Account(Id, Name), Contact(Id, Name)];
for (List<SObject> objList : soslResults)
{
for (SObject obj : objList)
{
System.debug('This is our object ::: ' + obj);
}
}That return type is the bit that throws people: List<List<SObject>>. A list of lists. One inner list per object type you asked for, in the order you listed them. So soslResults[0] is your Accounts and soslResults[1] is your Contacts.
They come back as generic SObject, so cast them when you need typed access:
List<Account> accounts = (List<Account>) soslResults[0];There’s a dynamic version too, using Search.query(), with all the same caveats as dynamic SOQL.
When To Use Which
Use SOQL when you know which object you want, you’re filtering on specific fields, or you need relationships or aggregates. Which is almost always.
Use SOSL when you’re doing a genuine text search, you don’t know which object holds the match, or you’re searching across many objects at once. Think a global search box.
SOSL is also better at text matching, since it uses a search index rather than scanning fields. If you’re doing WHERE Name LIKE '%something%' across a big object, SOSL will likely be faster.
The limits differ too: 20 SOSL queries per transaction, and a max of 2,000 records back.
What’s Next
All the code is on GitHub. Salesforce’s SOQL and SOSL Reference is genuinely one of their better docs and worth bookmarking.
One episode left. In the finale we cover asynchronous Apex: Future methods, Queueable, Batch and Schedulable, and how they let you escape a lot of the limits we’ve been carefully working around this whole series.
See you next time!
Get Coding With The Force Merch!!
We now have a redbubble store setup so you can buy cool Coding With The Force merchandise! Please check it out! Every purchase goes to supporting the blog and YouTube channel.
Get Shirts Here!
Get Cups, Artwork, Coffee Cups, Bags, Masks and more here!
Check Out More Coding With The Force Stuff!
If you liked this post make sure to follow us on all our social media outlets to stay as up to date as possible with everything!
Youtube
Patreon
Github
Facebook
Twitter
Instagram
Salesforce Development Books I Recommend
Advanced Apex Programming
Salesforce Lightning Platform Enterprise Architecture
Mastering Salesforce DevOps
Good Non-SF Specific Development Books:
Clean Code
Clean Architecture
