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'
//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 your query for the sForce Accounts' Contacts using Parent-to-Child format
// 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 ID
// 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, contactid FROM Cases) FROM Account WHERE name like '%United%' or name like '%Tyco%' order by name desc];
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 first account
System.Debug('Yes the cases are queried and we can show them');
System.Debug('Number of cases on third account: '+acctCases[2].Cases.size());
// Another way to is to query all account 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, contactid FROM Case];
// Let's get only the cases for the third account queried
List<Case> firstAcctCases = [SELECT id, subject, contactid FROM Case WHERE accountid = :acctNoCases[2].id];
System.Debug('Number of cases on thirs account queried: '+firstAcctCases.size());
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 a 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 'No Email'
//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!
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.