Tag: soql

Query Multi-Select Picklists in SOQL

Create list of values to check for multi picklist

       Set<String> typeSet = new Set<String>();
       String allStr = 'All';
       typeSet.add('\''+ allStr + '\'');
       for (Financial_Goal__c goal  : Contact.FinancialGoals__r){
         typeSet.add('\''+ goal.Type__c + '\'');
       }

Query using INCLUDES and using String.join to create string of List.

List<Content__c> typeMappings = (List<Content__c>) Database.query(
contentMappingQueryFactory.setCondition('Status__c=\'Publish\'
AND Id!=:featureMappingId AND (Goal_Type_Attribute__c INCLUDES(' + String.join(goalTypeList, ',')+ ') 
AND Age_Attribute__c INCLUDES(' + String.join(ageList, ',')+ ') 
AND Employment_Attribute__c IN :employementList AND Gender_Attribute__c IN :genderList 
AND Net_Worth_Attribute__c IN :netWorthList)').setLimit(2).toSOQL());

Use , for OR and ; for AND to match values in picklist

OR:
will match for any value in picklist
String.join(goalTypeList, ',')

AND:
will match for all the value in picklist
String.join(goalTypeList, ';')

SOQL self-join semi-join anti-join examples


Query Account from Contact using child-to-parent relationships:

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact

Sub Querying from Contact using parent-to-child relationships:

Select id, Name, Account.Name, Account.Owner.Name, Account.Owner.Profile.Name, RecordType.Name, 
(Select Opportunity.Name, Opportunity.StageName from Contact.OpportunityContactRoles), 
(Select id from Contact.Tasks), (Select Id from Contact.Cases), 
(Select Id from Contact.OpenActivities) from Contact

Outer Joins from Contact:

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where (Not Account.Owner.Profile.Name like 'Sales%')

Self-Join

Select c.id, c.Name, c.Account.Name, c.Account.Owner.Name,
c.Account.Owner.Profile.Name, c.RecordType.Name from Contact c 
where AccountId Not In (Select Id from Account 
where (Account.Owner.Profile.Name like 'Sales%'))

Semi-Join

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where Id In (Select ContactId from OpportunityContactRole
where Opportunity.StageName like 'Closed%')

Anti-Join

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact 
where Id Not In (Select ContactId from OpportunityContactRole where Opportunity.StageName like 'Closed%')

Combining Semi-Join and Anti-Join 

Select id, Name, Account.Name, Account.Owner.Name, 
Account.Owner.Profile.Name, RecordType.Name from Contact
where Id In (Select ContactId from OpportunityContactRole where Opportunity.StageName like 'Closed%')
and AccountId Not In (Select Id from Account where RecordType.Name='Partner')

Static and dynamic SOQL queries and best practices

If you are building Dynamic SOQL Queries that can be using for Apex Classes and Batch Apex you can use the Database.query(”) function to build the query:

      private static final String soqlQuery = 'Select id, OwnerId from Account';
      public void updateAccountOwnerByOwnerName(String currentOwnerName, String newOwnerName)
      {
         Map<Account> updateAccountWithNewOwnerName = new Map();
         List<Account> accountsAssignedToCurrentOwner = Database.query(soqlQuery + ' where Owner.Name like \' currentOwnerName + \');
         User getNewOwnerId = new User(Name=newOwnerName);
         for (Account accountsToUpdateOwner : accountsAssignedToCurrentOwner)
         {
           accountsToUpdateOwner.OwnerId = getNewOwnerId.Id;
           updateAccountWithNewOwnerName.put(accountsToUpdateOwner.OwnerId, accountsToUpdateOwner);
         }
        update updateAccountWithNewOwnerName.values();
      }

Non-dynamic soql or static soql looks like:

List<Account> accounts = [select id from account limit 100];

Apex Batch returns Database.QueryLocator or a Iterable.

private String query = 'Select id from Account';
return Database.getQueryLocator(query);

Combining non-dynamic SOQL with dynamic SOQL by converting from [ ] to a String. Using Database.getQueryLocator this can be done:

Database.getQueryLocator([select id from account] + ' where Owner.Name =' + newOwnerName);

Query SObject without burning SOQL queries

When you are writing Apex code you want to minimize the amount of SOQL queries in your code. One of the ways todo this is to create an SObject with some information of that object.

Let us say you have the following SOQL query:

Contact getAccountId = [Select AccountId from Contact limit 1];

Now you would like more information about the Account for that contact, you where thinking of taking the AccountId and running another SOQL query like this:

Account showAccountInfo = [Select Id from Account where Id=:getAccountId.AccountId];

You are burning unnecessary SOQL queries. You can do this following:

 Account showAccountInfo = new Account(Id=:getAccountId.AccountId);

You are not burning a SOQL query as you now have created an Object of that account. So you can do the following to query or update fields:

Get Information:

showAccountInfo.Name

Assign New Value:

showAccountInfo.Name='New Account Name'
update showAccountInfo;