+ Start a Discussion
James George 00700James George 00700 

Is it possible to do System.Debug dynamic SOQL statements

Hi Friends,
Is it possible or easy way to do System.Debug dynamic SOQL statements, want to know what will be the internal parameters build in the query before executing
For eg:
 
trigger AccountDeletion on Account (before delete) {
   
    // Prevent the deletion of accounts if they have related opportunities.
    for (Account a : [SELECT Id FROM Account
                     WHERE Id IN (SELECT AccountId FROM Opportunity) AND
                     Id IN :Trigger.old]) {
        Trigger.oldMap.get(a.Id).addError(
            'Cannot delete account with related opportunities.');
    }
    
}


Can somebody provide a sample code to have a debug log in the For loop, so I can know what are the parameters get passed.
or is there any way the SOQL query to be passed to a string so I can see what's going on.

Thanks,
James George

Best Answer chosen by James George 00700
Derrick AbbeyDerrick Abbey
Hi James,

Here's how I would write that trigger that you specified above.  In triggers, it's important to bulkify your trigger to avoid hitting governor limits, so I put the SOQL query before the loop.
trigger AccountTrigger on Account (before delete) {

    if(trigger.isDelete){
        //check the entire list of accounts to be deleted.
        System.debug(trigger.old);

        //query the accouts that have opportunities
        List<Account> accountsToBlockDeletion = [SELECT Id, Name 
                                                FROM Account
                                                WHERE Id IN (SELECT AccountId 
                                                            FROM Opportunity 
                                                            WHERE AccountId IN :trigger.oldMap.keySet())];//by filtering on the acocuntId the query runs more efficiently
        
        //loop through the results of the query
        for(Account a: accountsToBlockDeletion){
            //display the account that deletion is being blocked on in the debug logs
            System.debug(a);
            //add our error to prevent deletion.
            trigger.oldMap.get(a.Id).addError('Cannot delete an account that has an opportunity');
        }
    }
}
Regarding your question about dynamic SOQL, Here is the developer forum page: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm?search_text=soql
In a nutshell, you construct your query as a string (which you can easily display in a debug statement) and then use Datebase.query(yourQueryString) to execute.
Here's a very simple example:
String q = 'SELECT Id, Name FROM Account WHERE Name = \'Acme\'';
System.debug(q);
List<Account> queriedAccounts = Database.query(q);

 

All Answers

AbhishekAbhishek (Salesforce Developers) 
Hi James,

I suspect your query is answered in the below blogs,

https://developer.salesforce.com/forums/?id=9060G000000BfOQQA0

https://success.salesforce.com/answers?id=90630000000hdphAAA

https://webkul.com/blog/execute-a-dynamically-created-query-in-apex/


I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.


Thanks.
Derrick AbbeyDerrick Abbey
Hi James,

Here's how I would write that trigger that you specified above.  In triggers, it's important to bulkify your trigger to avoid hitting governor limits, so I put the SOQL query before the loop.
trigger AccountTrigger on Account (before delete) {

    if(trigger.isDelete){
        //check the entire list of accounts to be deleted.
        System.debug(trigger.old);

        //query the accouts that have opportunities
        List<Account> accountsToBlockDeletion = [SELECT Id, Name 
                                                FROM Account
                                                WHERE Id IN (SELECT AccountId 
                                                            FROM Opportunity 
                                                            WHERE AccountId IN :trigger.oldMap.keySet())];//by filtering on the acocuntId the query runs more efficiently
        
        //loop through the results of the query
        for(Account a: accountsToBlockDeletion){
            //display the account that deletion is being blocked on in the debug logs
            System.debug(a);
            //add our error to prevent deletion.
            trigger.oldMap.get(a.Id).addError('Cannot delete an account that has an opportunity');
        }
    }
}
Regarding your question about dynamic SOQL, Here is the developer forum page: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm?search_text=soql
In a nutshell, you construct your query as a string (which you can easily display in a debug statement) and then use Datebase.query(yourQueryString) to execute.
Here's a very simple example:
String q = 'SELECT Id, Name FROM Account WHERE Name = \'Acme\'';
System.debug(q);
List<Account> queriedAccounts = Database.query(q);

 
This was selected as the best answer
James George 00700James George 00700
Hi Derrick/Abhishek,
Thanks for the detailed reply and hint about hitting the governer limit.
Is there any documentation/reference about writing the complex SOQL which spans more than 2 SELECT statements in a single SOQL.

Thanks
James George
Derrick AbbeyDerrick Abbey
Hi James,

Here is the SOQL and SOSL reference guide. Introduction to SOQL and SOSL (https://developer.salesforce.com/docs/atlas.en-us.224.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm)
Here are a few of my most common queries that would span multiple objects.
//querying parent records based on criteria in child records, gets accounts with closed won opportunities
String q1 = 'SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE IsWon = true)';

//querying child records based on criteria on the parent record, gets contacts where account name is Acme
String q2 = 'SELECT Id, Name FROM Contact WHERE Account.Name = \'Acme\'';

//querying all Accounts of type 'customer' and their associated contacts, gets child records along with parent records
String q3 = 'SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Type = \'customer\'';

 
James George 00700James George 00700
Hi Derrick,
Thanks for providing the link and the sample SOQL.

Thanks again
James George
Nikita PetrovNikita Petrov

Hi Derrik,

Is it possible to substitute real values into a complex SOQL request like below:

let my_function_argument = ['a','b'];
String q1 = 'SELECT Id FROM Accounts WHERE My_Picklist__c IN :my_function_argument';
System.debug(q1); // Output: the same string with ':my_function_argument' without real array values