In the Salesforce ecosystem, developers frequently need to interact with data, troubleshoot issues, and optimize application performance. Salesforce Object Query Language (SOQL) is a powerful tool that allows developers to query data stored in Salesforce objects. Below, we highlight some useful SOQL queries that can be incorporated into daily workflows to enhance productivity and maintain system integrity.
- 1.Finding Records Without Related Data using Child Query
- 2. Tracking Field Changes by LastModifiedDate
- 3. Fetching Duplicate Records using Aggregate Query
- 4. Retrieve Apex Classes and VF Pages Using SetupEntityAccess
- 5. Query to Retrieve Field Names Using EntityParticle
- 6. Fetching Users Assigned to Permission Sets using PermissionSetAssignment
1.Finding Records Without Related Data using Child Query
A common task in ensuring data integrity is identifying records that are missing related data.
Query: Retrieve accounts with no associated contacts.
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact)
The subquery (SELECT AccountId FROM Contact) retrieves the AccountId. The NOT IN condition ensures that only accounts whose Id is not in this list are included in the results. In other words, it filters out accounts that have related contacts and returns only the accounts that do not have any associated contacts.
2. Tracking Field Changes by LastModifiedDate
Monitoring changes to data is critical for auditing and keeping data up-to-date.
Query: Retrieve accounts that were modified in the last 7 days, along with modification details.
SELECT Id, Name, LastModifiedBy.Name, LastModifiedDate
FROM Account
WHERE LastModifiedDate = LAST_N_DAYS:7
3. Fetching Duplicate Records using Aggregate Query
Duplicate records can lead to confusion and data redundancy, so identifying them is crucial. The below query finds and lists all account names that are duplicated in the Salesforce system.For each duplicate account name, it will show the name and how many accounts share that name.
Query: Identify accounts with duplicate names.
SELECT Name, COUNT(Id)
FROM Account
GROUP BY Name
HAVING COUNT(Id) > 1
SELECT Name: This part tells Salesforce to retrieve the Name field from the Account object, which refers to the account name.
COUNT(Id): This counts the number of records (or accounts) that share the same Name. The Id is used to count the number of instances of each account name.
GROUP BY Name: This clause groups the result set by the Name field. Essentially, it will group all accounts that have the same name together and aggregate them, allowing you to count how many accounts share that name.
HAVING COUNT(Id) > 1: The HAVING clause filters the results after the GROUP BY operation. In this case, it filters out any account names that appear only once.
COUNT(Id) > 1 ensures that only those account names that appear more than once (i.e., duplicates) are returned in the result set.
4. Retrieve Apex Classes and VF Pages Using SetupEntityAccess
The SetupEntityAccess object is a child of the PermissionSet object, functioning similarly to other child objects of PermissionSet, such as FieldPermissions and ObjectPermissions. To filter Apex classes by permission set, include ‘Parent.Name‘ in the WHERE clause.
Query: Retrieve Apex classes by permission set name.
SELECT Id, SetupEntity.Name, Parent.Name, Parent.NamespacePrefix, SetupEntityType
FROM SetupEntityAccess
WHERE SetupEntityType='ApexClass' AND Parent.Name = 'Permission_Set_Name'
5. Query to Retrieve Field Names Using EntityParticle
To retrieve field names using EntityParticle, you can run a query on the EntityParticle object within Salesforce’s Tooling API. Here’s how you can construct a query to get all fields for a specific object (for example, the Account object):
SELECT QualifiedApiName, DataType, Label
FROM EntityParticle
WHERE EntityDefinition.QualifiedApiName = 'Account'
QualifiedApiName – The API name of the field (e.g., Name, Industry, BillingAddress).
DataType – The type of the field (e.g., String, Picklist, Date, Lookup).
Label – The user-friendly label of the field displayed in the UI.
EntityDefinition.QualifiedApiName = ‘Account’ – Filters results to show fields only for the Account object.
6. Fetching Users Assigned to Permission Sets using PermissionSetAssignment
This SOQL query retrieves details about users and the permission sets assigned to them using the PermissionSetAssignment object.
SELECT PermissionSet.Name, Assignee.Name
FROM PermissionSetAssignment
Assignee.Name is simply the user’s name (who is assigned to the permission set).
To retrieve the ID of permission sets or users, use the PermissionSetId or AssigneeId fields in the query.
SELECT PermissionSet.Name, PermissionSetId, Assignee.Name, AssigneeId
FROM PermissionSetAssignment
