Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
@isTest (seeAllData = true)
public class SOQLqueries {
static testMethod void relationshipQueries(){
//Child-to-Parent queries are easy. Simply use the API Name of the lookup field to the parent object
//and use dot notation to specify the field on that object you want
List<Contact> conAccts = [SELECT FirstName, LastName, Account.Name FROM Contact];
//FIX ME! Use a query with a WHERE filter to only return Contacts from the Account named 'sForce'
//List<Contact> sForceconAccts = ???
//Here's an unexpected pattern for SOQL queries with Parent-to-Child records:
//You would expect that it might be a List<List> pattern so you could call Contacts
//with acctsCons[0][0] but instead you must use dot notation to reference the sub-lists
List<Account> acctCons = [SELECT Name, (SELECT FirstName, LastName FROM Contacts) FROM Account];
//FIX ME! Re-create the sForceconAccts List query for the sForce Accounts' Contacts but using Parent-to-Child format instead
// Let's try to query all Accounts with their child Cases in one query call
// For each Case that we want to query, we also want to find their Contact Name (another object!)
// Notice that this time we are not using 'Case' which is the object name but rather 'Cases' which is the relationship name!
List<Account> acctCases = [SELECT id, name, (SELECT id, subject, Contact.Name FROM Cases) FROM Account WHERE name like '%United%' or name like '%Tyco%'];
System.debug('Number of accounts: '+ acctCases.size());
System.debug('***Accounts and Cases queried, but only accounts shown on debug****');
// Let's get the number of cases from the 3rd account
System.Debug('But, yes the Cases are queried and we can show those results too if we ask for them');
System.Debug('Number of Cases on 3rd account: '+acctCases[2].Cases.size());
// Another way to do the same thing is to query all Accounts and all Cases into two separate lists,
// but that would require some additional work to get the same result.
List<Account> acctNoCases = [SELECT id, name FROM Account WHERE name like '%United%' or name like '%Tyco%' order by name desc];
List<Case> cases = [SELECT id, subject, contact.Name FROM Case];
// Let's get only the cases for the third account queried
List<Case> thirdAcctCases = [SELECT id, subject, contact.Name FROM Case WHERE accountid = :acctNoCases[2].id];
System.Debug('Number of cases on the 3rd account queried: '+ thirdAcctCases.size());
//What if we want all the Cases for all the Accounts we queried?
//We use the "IN" operator with our bind variable
List<Case> allTheCases = [SELECT id, subject, contact.Name FROM Case WHERE accountId IN :acctNoCases];
//FIX ME! Can you write the 3 queries that would get the same results as our "acctCons" query?
//List<Account> acctNoContacts = ???
//List<Contact> contacts = ???
//List<Contact> allTheContacts = ???
static testMethod void SOQLandCollections(){
//This only works when this test Class is set to SeeAllData=True!
List<Account> accts = [SELECT ID FROM Account];
//System.debug(accts[0].Name);//This won't work because Name was not acquired in the query.
accts = [SELECT ID, Name, Phone, BillingState FROM Account];
List<Contact> cons = [SELECT Department, MobilePhone, IsDeleted FROM Contact];
System.debug('smashing****************************' + cons[0].Id);
//Populating a Map from a SOQL Query is also easy, although not as intutive
//Note that you have to "cast" the query results (a List) to a Map
Map<Id, Account> acctMap1 = new Map<Id, Account>([SELECT ID, Name FROM Account]);
System.debug('Account Map: ' + acctMap1);
Map<Id, Contact> conMap1 = new Map<Id, Contact>(cons);
Map<Id, Contact> conMap2 = new Map<Id, Contact>([SELECT Id, Department, MobilePhone, IsDeleted FROM Contact]);
//Using Lists to update record instead of Maps
List<Account> acctList = [SELECT ID, Name, Industry, Ownership FROM Account];
List<Account> acctListToUpdate = new List<Account>();
//{Some logic here that determines Type must be updated on a record}
acctList[0].Type = 'Retailing';
//{Some other logic here that determines Rating must be updated on the same record}
acctList[0].Rating = 'Super Hot';
/* The update below compiles: you can save this class with it uncommented, but the class
* fails when we run the test because we added the same record to the list twice.
//update acctListToUpdate;//Fails with "Duplicate id in list" exception.
//Let's try the same thing, but using a Map to hold the records to be updated
Map<ID, Account> acctMapToUpdate = new Map<Id, Account>();
//We make the same field/values assigment as before on the same record
acctList[0].Type = 'Mineral Extraction';
//but now we put the record in a Map instead of a list
acctMapToUpdate.put(acctList[0].Id, acctList[0]);
//And we do it again for the Rating Field
acctList[0].Rating = 'Heavy';
//when we put it a second time, the Map recognizes that the ID already exists, and just
//amends the data in the Map with the new information
acctMapToUpdate.put(acctList[0].Id, acctList[0]);
update acctMapToUpdate.values();
//Let's clean up Contacts that lack phone numbers and emails
//1. Create a Query to get all Contacts that are missing both a phone number and email address
//2. Create an empty collection to hold records we want to update
//3. Set the Contacts' Phone Number to the Account's Phone Number
//4. Put the records into your update collection
//5. Now, since we don't know the email, let's set CleanStatus to 'Skippedl'
//6. Put the records into your update collection
//7. Use an "update" call to save the changes and commit them to the database
//8. Write a Unit Test to confirm it worked!

This comment has been minimized.

Copy link

commented Aug 4, 2016

Here's a link to code that you can call to build test data
The sample code is for Accounts, Contacts and Opportunities, but can be applied to any objects in your org. That way you can remove the "See All Data=True" which is not Best Practice for writing unit tests.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.