0

I have 2 SOQL queries based on the different 'order by'. Below are the queries

list<case> CaseTypeA = [Select Id, status, createddate , Priority from case where status = 'New' order by createddate]

There is another set of cases which need to be ordered based on Priority and createddate.

if(typeB){
   list<case> CaseTypeB = [Select Id, status, createddate , Priority from case where status = 'New'
   order by Priority,createddate]    
}

Is there a way, I can combine these 2 in a single SOQL query such that if typeB , then it orders based on Priority and createddate, else it orders based on the createddate?

1 Answer 1

6

To create a dynamic SOQL you could use Database.query method.
Just create a String variable with the ordering you should use, then append it to the query string.

String ordering = typeB ? 'Priority, CreatedDate' : 'CreatedDate';
List<Case> sortedCases = Database.query('Select Id, Status, CreatedDate, Priority FROM Case WHERE status = \'New\' ORDER BY ' + ordering);

Keep in mind that dynamic SOQL can’t use bind variable fields in the query string, only simple bind variables is allowed.
Invalid (binding a variable field):

Account myAccount = ...
// the following line will throw an error
Database.query('Select Id, Status FROM Case WHERE AccountId = :myAccount.Id'); 

Valid (binding just a variable):

Account myAccount = ...
Id accountId = myAccount.Id;
// the following line will be ok
Database.query('Select Id, Status FROM Case WHERE AccountId = :accountId'); 
3
  • 1
    the AccountId = :accountId is still a variable bind, it's just a simple one. Dynamic SOQL doesn't handle anything that needs additional processing, as it were (like getting the Id from an SObject variable instead of from an Id variable)
    – Derek F
    Commented Jul 18, 2021 at 22:16
  • 1
    @DerekF I think that's what RubenDG meant when they said "bind variable fields" - the fields can't be accessed. Commented Jul 19, 2021 at 12:48
  • 1
    @DerekF Viktor got it right, I just quoted the documentation, which makes a difference between "simple bind variables" (AccountId = :accountId) and "bind variable fields" (AccountId = :myAccount.Id). I edited the answer for more clarity
    – RubenDG
    Commented Jul 19, 2021 at 20:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .