Best Practice - Dynamic SOQL or Static Query within if/else

Question

I'm wondering what is the best practice in a situation where I have a conditional query - do I use an if/else to create my query string and use dynamic query in one place, or use static query and write the query out 2x. What is the ideal dev practice here? I always like to see static queries, but I hear that this really is the use-case for dynamic SOQL.

 Lead ref;
    if(checkWithSSN){
        ref = [SELECT Id FROM Lead WHERE Patient_SSN__C = :ssn ORDER BY CreatedDate DESC Limit 1];

    }else{
        ref = [SELECT Id FROM Lead WHERE FirstName =:firstName AND LastName = :lastName AND Patient_DOB__c = :dob ORDER BY CreatedDate DESC Limit 1];
    }

Or this

    if(checkWithSSN){
        queryStr += 'Patient_SSN__C = :ssn';
    }else{
        queryStr += 'FirstName = :firstName AND LastName = :lastName AND Patient_DOB__c = :dob'
    }
    queryStr += 'ORDER BY CreatedDate DESC Limit 1';
    List<Lead> refList = Database.query(queryStr);

sfdcfox · Accepted Answer · 2024-07-23 14:47:47Z

Inline queries are preferable to dynamic queries whenever possible. This preference is due to the fact that the compiler will prevent simple typos from sneaking past the developer. The main reason why you'd want dynamic queries is when the query is more or less under the user's control (e.g. imagine a component that lists records, and the user can choose which fields to show).

That said, here's where a difference of opinion comes in play. Using inline queries can make things complicated. In your case, having a binary option of filters is fine. However, imagine if you had 8 different filters that could all be configured independently. You'd need to write 256 (2⁸) different inline queries, which would not only be time-consuming, but error prone and hard to cover with unit tests.

So, conceptually, it generally comes down to whichever will be the most efficient to write and maintain. Dynamic queries typically require more testing to make sure they don't fail at runtime, but offer flexibility over writing a myriad of inline queries.

Stack Exchange Network

Best Practice - Dynamic SOQL or Static Query within if/else

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Best Practice - Dynamic SOQL or Static Query within if/else

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions