), you can execute the SOQL (Salesforce Object Query Language) query to get raw data from Salesforce, where some of this is unavailable from the report.
However, in some fields like the Id or polymorphic relationship field, we cannot use LIKE.
For Task and Event object, if you know the related object, you can filter with the object name
SELECT Id, WhatId, What.Type, What.Name FROM Task WHERE What.Type IN ('Account','Opportunity') LIMIT 100
Query filter on DateTime
SELECT Name FROM Account WHERE CreatedDate >= 2011-04-26T10:00:00-08:00 this is GMT-8
SELECT Name, CreatedDate FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z this is GMT
Query with Date function
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011
SELECT Amount FROM Opportunity WHERE CreatedDate = THIS_YEAR
Query filter on a null value
SELECT Id, AccountId FROM Event WHERE ActivityDate != Nullsame result using <>
SELECT Id, AccountId FROM Event WHERE ActivityDate <> Null
SELECT Id, AccountId FROM Event WHERE ActivityDate = Null
SELECT Id, AccountId FROM Event WHERE AccountId = Null
same result using '' SELECT Id, AccountId FROM Event WHERE AccountId = ''
But you can't use '' for the Date field and for the Number field
Query Multi-Select Picklists
SELECT Id, Country__c from Account WHERE Country__c INCLUDES ('Indonesia;Singapore','Malaysia')
Semi-Join Query
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost' )
Reference Field Semi-Join Query
SELECT Id FROM Task WHERE WhoId IN ( SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls' )
To query attachment from an object only
SELECT Id, Name, ParentId FROM Attachment WHERE ParentId IN ( SELECT Id FROM Account )
Anti-Join Query
SELECT Id FROM Account WHERE Id NOT IN ( SELECT AccountId FROM Opportunity WHERE IsClosed = False )
Reference Field Anti-Join Query
SELECT Id FROM Opportunity WHERE AccountId NOT IN ( SELECT AccountId FROM Contact WHERE LeadSource = 'Web' )
Multiple Semi-Joins Query
SELECT Id, Name FROM Account WHERE
Id IN ( SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%' ) AND
Id IN ( SELECT AccountId FROM Opportunity WHERE isClosed = False )
Relationship Query: parent to child
SELECT Id, (SELECT Id,Name from OpportunityLineItems) FROM Opportunity
SELECT Id, (SELECT Id,Name from Account_Requests__r) FROM Account
Notice for the child object name in yellow highlight, it is plural. You can get the name from the lookup field in the child object "
Child Relationship Name" and add
__r for custom field.
Relationship Query: child to parent
SELECT Id, Name, Account.Name FROM Contact
Relationship Query: Polymorphic
A polymorphic relationship field in an object being queried that can reference multiple object types. For example, the What relationship field of an Event or Task could be an Account, or a Campaign, or an Opportunity. Check references at the bottom of this blog.
SELECT Id FROM Task WHERE What.TYPE IN ('Account', 'Opportunity')
SELECT Id, WhatId,
TYPEOF What WHEN Account THEN BillingState, BillingCity END
FROM Task WHERE WhatId IN (SELECT Id FROM Account)
SELECT
TYPEOF What
WHEN Account THEN Phone, NumberOfEmployees
WHEN Opportunity THEN Amount, CloseDate
ELSE Name, Email
END
FROM Event
Select ALLIn standard SQL, we can use SELECT * from table_name, but this is not available in SOQL
In API 51.0, Salesforce introduced
FIELDS(ALL)—to select all the fields of an object.
FIELDS(CUSTOM)—to select all the custom fields of an object.
FIELDS(STANDARD)—to select all the standard fields of an object
Sample: SELECT FIELDS(ALL) FROM Account LIMIT 200
Using OFFSET
Use OFFSET to specify the starting row offset into the result set returned by your query.
SELECT Id, Name FROM Opportunity ORDER BY Name OFFSET 5
OFFSET 0 means no record is removed in the result, OFFSET 1 means the first row will not be shown, etc.
Using NULLS {FIRST|LAST}
If records are null, you can use ORDER BY to display the empty records first or last. By default, null values are sorted first. You can use ORDER BY with the optional LIMIT qualifier in a SELECT statement.
SELECT Name FROM Account WHERE industry = 'media' ORDER BY BillingPostalCode ASC NULLS LAST LIMIT 125
Using COUNT
Returns the number of rows matching the query criteria
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'
If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT()
Using COUNT_DISTINCT
Returns the number of distinct non-null field values matching the query criteria
SELECT COUNT_DISTINCT(Company) FROM Lead
This will return the number of distinct values from the Company field
Using GROUP BY
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM(), MAX(), MIN(), AVG()
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
SELECT StageName, SUM(amount), MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName
SELECT CALENDAR_YEAR(CloseDate) Year, CALENDAR_MONTH(CloseDate) Month, COUNT(Id) TotalCount FROM Opportunity WHERE IsClosed = False GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate) ASC
Note: you cannot use a formula field for grouping.
Using Alias with GROUP BY
You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code.
SELECT StageName, SUM(amount) Jumlah, MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName ORDER BY SUM(amount) Desc
Using GROUP BY ROLLUP
Same with GROUP BY, with additional ROLLUP, it adds subtotal for aggregated data in the last row
SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY ROLLUP(StageName)
Using GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNT(Id) FROM Lead GROUP BY ROLLUP(Status, LeadSource)
*Yellow highlight = total subgroup
*Green highlight = total all
HAVING in GROUP BY
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, same with WHERE with normal query
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource HAVING COUNT(Id) > 2
Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name, Amount, CurrencyIsoCode FROM Opportunity WHERE Amount > SGD5000 ORDER BY Amount
** without currency code it will use the organization's default currency
** Amount in query result will be in record currency
Reference:
