How to query parent record picklist field value in dynamic WHERE clause

Question

I want to develop a WHERE clause for a dynamic SOQL query in a utility class. The WHERE clause should lookup a parent record picklist field value.The relationship between Shared_Contact__c and Contact is a standard lookup. The field I'm lookuping up is named Status__c and on Contact object. I want to lookup the Active picklist value.

When I change my code it won't save and gives me the following error, though I don't understand how else I should be querying for this type of record?

Error:

Compile Error: Missing ';' at 'Active' at line 273 column 68

public class RecordMatchUtilities
{
@TestVisible
private String CUSTOM_SETTING_CDFINTEGRATION = 'CDFIntegration';
@TestVisible
private CDF_Integration__c CDFParams = CDF_Integration__c.getInstance(CUSTOM_SETTING_CDFINTEGRATION);

private CDFIntegration cdfIntegration;
@TestVisible
private Map<String,String> paramsAndValues;
@TestVisible
private List<Integer> scoreOrder;
private String CONTACT_RELATIONSHIP = Shared_Contact__c.Contact__c.getDescribe().getRelationshipName();
private String ACCOUNT_RELATIONSHIP = Shared_Contact__c.Account__c.getDescribe().getRelationshipName();

public RecordMatchUtilities(String firstNameParam, String lastNameParam, String jobTitleParam, String phoneParam, String faxParam, String emailParam, String mailingStreetParam, 
    String mailingCityParam, String mailingStateParam, String mailingCountryParam, String mailingPostalCodeParam, String jiscIdParam, String accountJiscIdParam, String contactStatusParam)
{
    this(firstNameParam, lastNameParam, jobTitleParam, phoneParam, faxParam, emailParam, mailingStreetParam, 
        mailingCityParam, mailingStateParam, mailingCountryParam, mailingPostalCodeParam, jiscIdParam, accountJiscIdParam, contactStatusParam, new CDFIntegration());
}   

@TestVisible
private RecordMatchUtilities(String firstNameParam, String lastNameParam, String jobTitleParam, String phoneParam, String faxParam, String emailParam, String mailingStreetParam, 
    String mailingCityParam, String mailingStateParam, String mailingCountryParam, String mailingPostalCodeParam, String jiscIdParam, String accountJiscIdParam, String contactStatusParam, 
    CDFIntegration cdfIntegration)
{
    this.cdfIntegration = cdfIntegration;
    initialiseParamsMap(firstNameParam, lastNameParam, jobTitleParam, phoneParam, faxParam, emailParam, mailingStreetParam, 
        mailingCityParam, mailingStateParam, mailingCountryParam, mailingPostalCodeParam, jiscIdParam, accountJiscIdParam, contactStatusParam);
}

private void initialiseParamsMap(String firstNameParam, String lastNameParam, String jobTitleParam, String phoneParam, String faxParam, String emailParam, String mailingStreetParam, 
    String mailingCityParam, String mailingStateParam, String mailingCountryParam, String mailingPostalCodeParam, String jiscIdParam, String accountJiscIdParam, String contactStatusParam )
{
    paramsAndValues = new Map<String,String>();
    if(String.isNotBlank(firstNameParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.FirstName', firstNameParam);
    }
    if(String.isNotBlank(lastNameParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.LastName', lastNameParam);
    }
    if(String.isNotBlank(jobTitleParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Title', jobTitleParam);
    }
    if(String.isNotBlank(phoneParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Phone', phoneParam);
    }
    if(String.isNotBlank(faxParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Fax', faxParam);
    }
    if(String.isNotBlank(emailParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Email', emailParam);
    }
    if(String.isNotBlank(mailingStreetParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.MailingStreet', mailingStreetParam);
    }
    if(String.isNotBlank(mailingCityParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.MailingCity', mailingCityParam);
    }
    if(String.isNotBlank(mailingStateParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.MailingState', mailingStateParam);
    }
    if(String.isNotBlank(mailingCountryParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.MailingCountry', mailingCountryParam);
    }
    if(String.isNotBlank(mailingPostalCodeParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.MailingPostalCode', mailingPostalCodeParam);
    }
    if(String.isNotBlank(jiscIdParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Jisc_ID__c', jiscIdParam);
    }
    if(String.isNotBlank(accountJiscIdParam))
    {
        paramsAndValues.put(ACCOUNT_RELATIONSHIP + '.Jisc_ID__c', accountJiscIdParam);
    }
    if(String.isNotBlank(contactStatusParam))
    {
        paramsAndValues.put(CONTACT_RELATIONSHIP + '.Status__c', contactStatusParam);
    }
}

/*
    Executes the Contact match.
*/
public List<String> processRecordMatch()
{
    Map<Integer,List<Shared_Contact__c>> scoreMap = buildContactMatchScore(getMatchingContacts());
    return getTopMatchingRecords(scoreMap);
}

/*
    This method extracts the Top Matching Contacts, specified in the Contact_Match_List_Size__c 
    field in the CDFIntegration custom setting.
*/
public List<String> getTopMatchingRecords(Map<Integer,List<Shared_Contact__c>> scoreMap)
{
    Map<String,String> uniqueContactJiscIds = new Map<String,String>();
    List<String> theContactJiscIds = new List<String>();
    if(scoreOrder!= null && !scoreOrder.isEmpty())
    {
        // This index is used to retrieve the Keys in order from the highest score.
        Integer scorePosition = scoreOrder.size() - 1;

        // This index is used to limit the number of contacts returned.
        Integer numberOfRecords = Integer.valueOf(CDFParams.Contact_Match_List_Size__c) - 1;

        while(scorePosition >= 0 && numberOfRecords >= 0)
        {
            List<Shared_Contact__c> aListOfContacts = scoreMap.get(scoreOrder.get(scorePosition));
            aListOfContacts.sort();
            for(Shared_Contact__c aSharedContact : aListOfContacts)
            {
                if(numberOfRecords >= 0 && !uniqueContactJiscIds.containsKey(aSharedContact.Contact__c))
                {
                    uniqueContactJiscIds.put(aSharedContact.Contact__c, aSharedContact.Contact__r.Jisc_ID__c);
                    theContactJiscIds.add(aSharedContact.Contact__r.Jisc_ID__c);
                    numberOfRecords--;
                }
            }
            scorePosition--;
        }
    }
    System.debug('### Matched ' + theContactJiscIds.size() + ' Contacts: ' + theContactJiscIds);
    return theContactJiscIds;
}

/*
    This method groups the records by number of field matches.
    A Score list is created to be able to sort the matching scores.
    The scores are calculated by adding one point for a "LIKE" match 
    and two points for an exact match.
*/
public Map<Integer,List<Shared_Contact__c>> buildContactMatchScore(List<Shared_Contact__c> theContacts)
{
    Map<Integer,List<Shared_Contact__c>> contactMatchScore = new Map<Integer,List<Shared_Contact__c>>();

    if(theContacts != null)
    {
        // This list holds the values of the matching score, used to order the list of contacts to be returned.
        List<Integer> orderOfScore = new List<Integer>();

        // Loop through all of the matched Contacts
        for(sObject aMatchedRecord : theContacts)
        {
            // Calculate the matching fields for the current Contact
            Integer thisScore = 0;
            for(String matchingField : paramsAndValues.keySet())
            {
                if(matchingField.startsWith(CONTACT_RELATIONSHIP))
                {
                    thisScore = checkFieldMatch((Contact)aMatchedRecord.getSObject(CONTACT_RELATIONSHIP), matchingField, thisScore);
                }
                else if(matchingField.startsWith(ACCOUNT_RELATIONSHIP))
                {
                    thisScore = checkFieldMatch((Account)aMatchedRecord.getSObject(ACCOUNT_RELATIONSHIP), matchingField, thisScore);
                }
            }
            System.debug('#### Score: ' + thisScore);

            if(!contactMatchScore.containsKey(thisScore))
            {
                contactMatchScore.put(thisScore, new List<Shared_Contact__c>{(Shared_Contact__c)aMatchedRecord});
            }
            else
            {
                contactMatchScore.get(thisScore).add((Shared_Contact__c)aMatchedRecord);
            }
        }
        if(!contactMatchScore.isEmpty())
        {
            scoreOrder = new List<Integer>(contactMatchScore.keySet());
            scoreOrder.sort();
        }
    }

    return contactMatchScore;
}

/*
    This method checks whether the matching field hits a score for the input record, 
    which can be a Contact or an Account.
*/
private Integer checkFieldMatch(sObject record, String matchingField, Integer thisScore)
{
    String theFieldName = matchingField.substringAfter('.');
    if(record != null && record.get(theFieldName) != null && ((String)record.get(theFieldName)).containsIgnoreCase(paramsAndValues.get(matchingField)))
    {
        System.debug('#### Matched on: ' + matchingField + ' (' + paramsAndValues.get(matchingField) + ').');
        thisScore++;
        // An exact match increases the score
        if(((String)record.get(theFieldName)).equalsIgnoreCase(paramsAndValues.get(matchingField)))
        {
            thisScore++;
        }
    }
    return thisScore;
}

/*
    This method retrieves the Shared Contact records, together with the related Contact and Account fields, 
    based on the query that filters them according to the parameters specified in the Web Service request.
*/
public List<Shared_Contact__c> getMatchingContacts()
{
    List<Shared_Contact__c> matchedSharedContacts = null;
    if(paramsAndValues != null && !paramsAndValues.isEmpty())
    {
        String sharedContactQueryString = 
            'SELECT ' + CONTACT_RELATIONSHIP + '.Jisc_ID__c ' + buildSelectClause() + 
            'FROM Shared_Contact__c ' + 
            buildWhereClause() ;

        System.debug('######### sharedContactQueryString: ' + sharedContactQueryString);

        matchedSharedContacts = Database.query(sharedContactQueryString);

        System.debug('######### matchedSharedContacts: ' + matchedSharedContacts);
    }
    return matchedSharedContacts;
}

/*
    This method creates a dynamic SELECT clause for the SOQL query depending on the 
    key-value pairs containing the Contact field and the value to compare with.
*/
public String buildSelectClause()
{
    String selectClause = '';
    if(paramsAndValues != null && !paramsAndValues.isEmpty())
    {
        for(String aParam : paramsAndValues.keySet())
        {
            if(!aParam.equals(CONTACT_RELATIONSHIP + '.Jisc_ID__c'))
            {
                selectClause += ', ' + aParam;
            }
        }
        selectClause += ' ';
    }
    return selectClause;
}

/*
    This method creates a dynamic WHERE clause for the SOQL query depending on the 
    key-value pairs containing the Contact field and the value to compare with.
*/
public String buildWhereClause()
{
    String whereClause = '';
    if(paramsAndValues != null && !paramsAndValues.isEmpty())
    {
        for(String aParam : paramsAndValues.keySet())
        {
            if(whereClause.contains('WHERE'))
            {
                whereClause +=  ' OR (' + aParam + ' = \'' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '\' OR ' + aParam + ' LIKE \'%' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '%\')  ';
            }
            else
            {
                whereClause += 'WHERE (Contact__r.Status__c = 'Active') AND (' + aParam + ' = \'' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '\' OR ' + aParam + ' LIKE \'%' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '%\') ';
            }
        }
    }
    return whereClause;
}

}

Answer 1

As you have done it multiple times, you need to escape the single quote. You forgot to escape quote in Active, Status condition.

'WHERE (Contact__r.Status__c = \'Active\') AND (' + aParam + ' = \'' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '\' OR ' + aParam + ' LIKE \'%' + String.escapeSingleQuotes(paramsAndValues.get(aParam)) + '%\') ';