Filtering SOQL results with optional conditionals

Question

I am creating a web component that allows users to filter the results of some data based on some optional filtering fields. In SOQL, how do you handle returning results when a filter is not set?

For example, I have a Student__c object. I want users to be able to filter by the the Student__c's Field A (String), Field B (Integer) and Field C (String).

I have this function:

function getStudents(nationality, school, birthYear) {
    return [SELECT * FROM Student__c WHERE ....];
}

I call this function to load some data to display in a graph to a user. The user doesn't have to filter the students by nationality, school, and birthYear, so by default, these values will be null, and should return the results of all students. But if the user opts to filter by birthYear, then it should filter by birthYear. If they want to filter by birthYear and school, then it should do so.

I'm trying to avoid having to do something like:

function getStudents(nationality, school, birthYear) {
    if (nationality == null && school == null, && birthYear == null) {
        return [SELECT * FROM Student__c];
    } else if (nationality != null && school == null, && birthYear == null) 
        return [SELECT * FROM Student__c WHERE nationality__c == :nationality];
    }
    else if ( .... ) {
        ...
}

Instead of having different queries for the different scenarios (3 field filters is 8 different queries, 4 fields is 15 different, etc.) I would like my query to handle it all. I was thinking about using a contains, but SOQL doesn't have that from what I believe...

Answer 1

This is a great opportunity to apply Dynamic SOQL. Here's one way to approach it.

Start with a query template string, with placeholders for your dynamic entries:

String queryTemplate = 'SELECT [your list of fields] FROM Student__c {1} {2}';

We'll dynamically construct the content of the WHERE clause based on data. We'll use an extra merge field to allow us to cope with the possibility that no filters are provided at all, and we don't need a WHERE clause.

Next, we would build up a list of WHERE subclauses based on the passed information. For example, you might have something like this:

List<String> whereClauses = new List<String>();

if (nationality != null) {
    whereClauses.add('Nationality__c = :nationality');
}
if (school != null) {
    whereClauses.add('School__c = :school');
}
if (birthYear != null) {
    whereClauses.add('Birth_Year__c = :birthYear');
}

Then, once the list is complete, we can construct a final query and issue it dynamically:

return Database.query(
    String.format(
        queryTemplate,
        new List<String> {
            whereClauses.size() > 0 ? 'WHERE' : '',
            String.join(whereClauses, ' OR ')
        }
    )
);

This keeps your logic flat, and avoids the combinatorial madness of trying to account for every possible combination of input parameters. It also - provided you issue the query in the same method or scope where you construct it - allows you to use Apex binds, so you avoid having to worry about type conversion and SOQL injection defense.

I like to go so far as to dynamically construct my SELECT clause too, from a List<Schema.SobjectField>. That way, my code retains a static, compile-time reference to the field, and I get proper metadata dependency tracking. It also streamlines enforcement of FLS.