1

I am hoping for some help with a conceptual problem on the platform.

As a best practice, I try to bulkify all of my logic in a service class to force callers to send a list of parameters. In certain situations, I'll create a wrapper class to manage the parameters to enforce what is needed for the service method.

The purpose of the solution is to take some search criteria, match it to a record, then return the children record. This works great in a non-bulkified situation:

public List<MyTemplateObject> searchTemplate(String fieldSearch1, String fieldSearch2, String fieldSearch3)
{
     List<MyTemplateObject> results = [SELECT Id, (SELECT Id FROM TemplateChild) FROM TemplateObject WHERE Field1 = :fieldSearch1 AND Field2 = :fieldSearch2 AND Field3 = :fieldSearch3];
}

My problem occurs when I have a wrapper like so:

public class SearchWrapper
{
    public String fieldSearch1 {get;set;}
    public String fieldSearch2 {get;set;}
    public String fieldSearch3 {get;set;}

    public SearchWrapper()
    {

    }
}

And create a method like so:

public List<MyTemplateObject> searchTemplate(List<SearchWrapper> searchCriteria)
{

}

I've thought of the following:

  1. Loop through searchCriteria, add values to multiple sets and do one SOQL query. Then iterate over each searchCriteria and within that iterate through each result and attempt to find a match for each search.

I am finding however, that this seems extremely inefficient and I have to believe the is a better way. Does anyone have a suggestion for how to improve on my initial approach?

1 Answer 1

5

First you need to tie down the meaning of the list of wrappers. My assumption (but ultimately its your choice as the designer/documentor of the API) is that you want only records where all 3 wrapper values match not where any permutation of the 3 wrapper values match.

One way to do that is dynamic SOQL:

String[] terms = new String[] {};
for (SearchWrapper sc : searchCriteria) {
    String term = '(' + String.join(new String[] {
            'Field1 = \'' + String.escapeSingleQuotes(sc.fieldSearch1) + '\'',
            'Field2 = \'' + String.escapeSingleQuotes(sc.fieldSearch2) + '\'',
            'Field3 = \'' + String.escapeSingleQuotes(sc.fieldSearch3) + '\''
            }, ' and ') + ')';
    terms.add(term);
}

... Database.query('select ... from ... where ' + String.join(terms, ' or '));

Or (if the object and fields is fixed) you can create a formula field that combines the 3 fields and query on the set of values combined in Apex in the same way:

Set<String> values = new Set<String>();
for (SearchWrapper sc : searchCriteria) {
    // Same concatenation as the formula field
    values.add(sc.fieldSearch1 + '::' + fieldSearch2 + '::' + scfieldSearch3);
}

... = [select ... from ... where FormulaField__c in :values];
1
  • Thanks Keith! I ended up going with the second option. I deviated a little and used a workflow rule to populate a text field with the criteria fields as every record must be unique and it's easy to enforce it that way. Thanks for the answer! Commented Aug 16, 2017 at 23:50

You must log in to answer this question.

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