Tag Archives: SOQL

SOQL Statement to Select All Records Even From Recycle Bin

SOQL statements can use the ALL ROWS keywords to query all records in an organization, including deleted records and archived activities.

For Example:

[SELECT COUNT() FROM Contact WHERE AccountId = '0030E00000D34jq' ALL ROWS]

You can use ALL ROWS to query records in your organization’s Recycle Bin. You cannot use the ALL ROWS keywords with the FOR UPDATE keywords.

Lock Records Using Apex in Salesforce

In Apex, to lock records, simply use the FOR UPDATE keyword in your SOQL statements. While an sObject record is locked, no other client or user is allowed to make updates either through code or the Salesforce user interface. The client locking the records can perform logic on the records and make updates with the guarantee that the locked records won’t be changed by another client during the lock period. The lock gets released when the transaction completes.

Note: FOR UPDATE does not work to lock User object records.

For example, the following statement, in addition to querying for 5 Account record, also locks the accounts that are returned:

List<Account> accList = [SELECT Id, Name From Account LIMIT 5 FOR UPDATE];

Locking Considerations:

  • While the records are locked by a client, the locking client can modify their field values in the database in the same transaction. Other clients have to wait until the transaction completes and the records are no longer locked before being able to update the same records. Other clients can still query the same records while they’re locked.
  • If you attempt to lock a record currently locked by another client, your process waits for the lock to be released before acquiring a new lock. If the lock isn’t released within 10 seconds, you will get a QueryException. Similarly, if you attempt to update a record currently locked by another client and the lock isn’t released within 10 seconds, you will get a DmlException.
  • If a client attempts to modify a locked record, the update operation might succeed if the lock gets released within a short amount of time after the update call was made. In this case, it is possible that the updates will overwrite those made by the locking client if the second client obtained an old copy of the record. To prevent this from happening, the second client must lock the record first. The locking process returns a fresh copy of the record from the database through the SELECT statement. The second client can use this copy to make new updates.

BigObjects in Saleforce

What is BigObject?

  • BigObject means 100s of billions of record on AppCloud.
  • It is a new capability with highly scalable object to store and manage large amount of data on the Salesforce platform.
  • This feature helps to engage directly with customers by preserving all your historical customer event data.
  • BigObjects are built by Salesforce to provide consistent performance whether there is 1 million records, 100 million, or even 10 billion records.

Use Cases:

  • Audit and Tracking: Track and maintain a long-term view of your user’s usage of Salesforce or your customer’s usage of your products for analysis or compliance purposes.
  • 360 view of Customers: Now data models can be extended to contain billing infos/ecommerce transactions/any other info related to customers.
  • Historical Data Archive: Maintain access to historical data for analysis or compliance purposes while optimizing the performance of your core CRM or Force.com applications.

Considerations:

  • It is provided as a pilot to selected premium customers.
  • The BigObjects cannot be created from the UI. We have to take help of the Metadata APIs to create one.
  • BigObjects don’t support triggers however they support object and field permissions.
  • BigObject don’t support standard UI elements (Home Pages, Detail Pages, List Views), But It can be used in Visualforce Page and Lightning components.
  • Data can be populated to the BigObjects via SFDC APIs/Bulk APIs/using Apex(insertImmediate() method).
  • The only SOQL relationship queries available are based on a lookup field from a BigObject to a standard or custom object.
  • Mostly Async SOQL would be used for the querying the BigObjects. Single level child-to-parent relationship queries and aggregate queries are supported.
  • BigObjects don’t support transactions.
  • We can create up to 100 BigObjects per org. The limits for BigObject fields are similar to the limits on custom objects, and depend on your org’s license type.
  • BigObjects don’t appear in the Setup UI until they are deployed.
  • BigObjects don’t appear in Salesforce1.

SOQL Query To Get Users with Salesforce User License

Get all Users with User License:

List<User> userList = [Select Id, Name, Profile.UserLicense.Name From User];

Get Users with specific User License:

List<User> userList = [Select Id, Name, Profile.UserLicense.Name From User WHERE Profile.UserLicense.Name = 'Salesforce'];

System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set.

For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}

In order to avoid System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop, make sure the sub query is limited as follows.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts LIMIT 200) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}