SOQL – How to Convert String to Query in Dynamic SOQL

Can anyone help me on how to convert the String q1 into a query? I am trying to put my variable 'q1' inside the [] but I always got an error "Unexpected token q1"

Heres the code.

String q1 = 'SELECT count() FROM Case WHERE '+q;
Integer totalCount = 0;
totalCount = [q1];   

public Case[] getCases() {

    Map<Id, BusinessProcess> bp = new Map<Id, BusinessProcess>([SELECT Id FROM BusinessProcess WHERE Name IN ('MSP', 'Provisioning', 'On-Boarding')]);

    Map<Id, RecordType> r = new Map<Id, RecordType>([SELECT Id, Name FROM RecordType WHERE SObjectType = 'Case' AND BusinessProcessId IN :bp.keySet()]);

    String q = '';
    if (view.startsWith('all'))
        q += 'AccountId = :acctId';
    else
        q += 'ContactId = :conId';

    if (view.endsWith('closed'))
        q += ' AND IsClosed = true';
    else
        q += ' AND IsClosed = false';

    if (view == 'waiting')
        q += ' AND Status = \'Awaiting Customer Response\'';

    if (String.isNotBlank(query))
        q += ' AND (Subject LIKE \'%'+query+'%\' OR Product__c LIKE \'%'+query+'%\' OR CaseNumber LIKE \'%'+query+'%\' OR Product__c LIKE \'%'+query+'%\' OR Case_Type__c LIKE \'%'+query+'%\' OR MaxDeviceName__c LIKE \'%'+query+'%\' OR Device_Name__C LIKE \'%'+query+'%\' OR N_Central_Server__c LIKE \'%'+query+'%\')';

    if(!r.isEmpty()){
        Set<Id> recordTypeSet = r.keySet();
        q += ' AND RecordTypeId IN :recordTypeSet';
    }

    String queryFields = 'Id, Priority';
    for (Schema.FieldSetMember field : getFields())
        queryFields += ',' + field.getFieldPath();

    if (selectedPage != '0') counter = list_size*integer.valueOf(selectedPage)-list_size;

    if(String.isBlank(sortOrder) && String.isBlank(sortField)){
        q = 'SELECT '+queryFields+' FROM Case WHERE ' + q + ' ORDER BY CaseNumber DESC limit '+list_size+' offset '+counter;


    }else{
        q = 'SELECT '+queryFields+' FROM Case WHERE ' + q + ' ORDER BY ' + sortField + ' ' + sortOrder + ' limit '+list_size+' offset '+counter;
    }

    String q1='SELECT count() FROM Case WHERE '+q;

    Integer totalCount =[q1];


      Case[] cases = Database.query(q);

        for (Case cs : cases) {

            if (!productUtil.containsKey(cs.Product__c))
                productUtil.put(cs.Product__c,cs.Product__c);
        }

     return cases;


}

Best Answer

Normally you could just use Database.query(queryString) to make a query from a string.

However, that method only returns a List<SObject> that cannot be cast to a Integer. Since you are looking for a count() you need to use Database.countQuery().

String q1 = 'SELECT count() FROM Case WHERE '+q;
Integer totalCount = Database.countQuery(q1);