Combine Multiple Filters in a Single SQL Query

Photo by Nathan Dumlao on Unsplash

Let’s say you have an Android app that shows a list of tasks:

By default, we show all tasks. But the user can select a combination of filters, such as:

• A range of due dates.
• Only incomplete tasks.
• Only tasks of a particular priority (“high”, “medium”, “low” or no priority).

In our app, our tasks are represented in a SQLite database with a single table:

tasks
    id: INTEGER, NOT NULL
    name: TEXT, NOT NULL
    dueDate: INTEGER (format: YYYYMMDD)
    priority: INTEGER (2, 1, 0 or NULL)
    isComplete: INTEGER NOT NULL (1=DONE, 0=NOT DONE)

We query our SQLite database with a Room DAO.

To query for all tasks, we can do the following:

@Query("select * from tasks")
fun getAll(): List<Task>

(Or we can use an observable version of this.)

Now if the user wants to query for only a particular due date range, we can have a DAO query like this:

@Query("""select * from tasks
    where dueDate >= :startDate and dueDate <= :endDate""")
fun getByDueDateRange(
    startDate: Date,
    endDate: Date
): List<Task>

And we can have specific queries for the other filtering options.

By themselves, that means we would now have 4 functions in our DAO: 3 for our filtering options and 1 to show all tasks:

@Query(...)
fun getAll()
@Query(...)
fun getByDueDateRange(...)
@Query(...)
fun getOnlyIncomplete(...)
@Query(...)
fun getByPriority(...) 

But, users can combine filters, so we would actually have 8 functions we would need to write:

@Query(...)
fun getAll()
@Query(...)
fun getByDueDateRange(...)
@Query(...)
fun getOnlyIncomplete(...)
@Query(...)
fun getByPriority(...)
@Query(...)
fun getByDueDateRangeAndOnlyIncomplete(...)
@Query(...)
fun getByDueDateRangeAndPriority(...)
@Query(...)
fun getByPriorityAndOnlyIncomplete(...)
@Query(...)
fun getByDueDateRangePriorityAndOnlyIncomplete(...)

Not only is this a combinatoric explosion in our DAO, it also means that the calling code would need to have awful branching logic to decide which function to call.

The E Pluribus Unum Query

Well, it turns out we can combine all of the above 8 functions into one, and handle all combinations of filters that the user may provide (including none). We can do this with a little SQL trick:

@Query("""select * from tasks
    where (0 = :withDateRange or
        (dueDate >= :startDate and dueDate <= :endDate)
    )
    and (0 = :withPriorityFilter or (priority = :priority))
    and (0 = :incompleteOnly or (isComplete = 0))""")
fun getTasks(
    withDateRange: Boolean,
    withPriorityFilter: Boolean,
    incompleteOnly: Boolean,
    priority: Int?, // See below...   
    startDate: Int?, // format YYYYMMDD
    endDate: Int? // format YYYYMMDD
): List<Task>

Here’s what we’re doing:

• We supply some flags indicating whether we want to use a particular filter: withDateRange, withPriorityFilterand incompleteOnly.
• We supply the actual filter parameters priority, startDate and endDate.
• Each filter is specified in the WHERE clause of the SQL statement, joined with AND.
• Each filter clause is written in the format of (0 = :filterFlag or (filterClause))

Let’s look at the filter clause a bit more. If the filter flag is true (translates to 1 when run in SQLite), it makes the expression (0 = 1 or (filterClause)). This means that, in order for the expression to be true, filterClause must be true. In other words, we actually use this filter, andfilterClause must be true in order for the row to be returned in the SQL statement. If, however, the filter flag is false (translates to 0), it makes the expression (0 = 0 or (filterClause)). It effectively turns the filterCluase into a “don’t care” because this is a or statement. Thus, if the filter flag is false, the filter is not run. It does not influence the rows that are returned.

So what we have effectively done is combined all of our filters into one SQL statement, but with the use of these flags in the or statements, it allows us to select any combination of these filters when running the SQL statement. We’ve eliminated the combinatoric explosion in our DAO as well as any complex branching logic the caller would need.

Parameter Congruity

It’s important to note, however, that this now means we need to ensure congruity between the flags and their corresponding filter parameters. For example, if withPriorityFilter is specified, we need to ensure we supply a valid value for priority (in other words, that the caller doesn’t forget to supply it). It would perhaps be less error prone if we simply provided priority and checked for null instead:

(0 = (:priority is not null) or (priority = :priority))

Or simplified:

(:priority is null or (priority = :priority))

And that would work great if the priority column was non-null. But it is nullable, and we may in fact want to filter for those tasks that have no priority, so the above would not work for that case. As such, we probably still need these congruous parameters.

Filtering for NULL Values

Speaking of which, we just said we want to be able to have this filter work to only show tasks with no priority, which we represent as NULL in that column. Well, if we pass withPriorityFilter = true and priority = null, our SQL clause for that will be:

(0 = 1 or (priority = null))

That actually won’t work. Equality comparisons to null in SQL don’t work; they return false. Instead, if our filter parameter is null we need to handle it differently: we need to use is null. One way to handle that is with a case statement:

(0 = :withPriorityFilter or (case when :priority is null then priority is null else priority = :priority end))

Another option is to cast null values to comparable values, and select a value that is known to be invalid. In our case, priority values can only be 0, 1, 2 or null, so perhaps we can cast null to -1:

(0 = :withPriorityFilter or (ifnull(priority, -1) = ifnull(:priority, -1)))

Another option: avoid this mess altogether and just make this column non-null and define one of our integer values to mean “no priority”!

The Final E Pluribus Unum Query

We’ll go with the case statement approach for now. So our final DAO query looks like this:

@Query("""select * from tasks
    where (0 = :withDateRange or
        (dueDate >= :startDate and dueDate <= :endDate)
    )
    and (0 = :withPriorityFilter or
        (case when :priority is null
            then priority is null
            else priority = :priority end
        )
    )
    and (0 = :incompleteOnly or (isComplete = 0))""")
fun getTasks(
    withDateRange: Boolean,
    withPriorityFilter: Boolean,
    incompleteOnly: Boolean,
    priority: Int?,    
    startDate: Int?, // format YYYYMMDD
    endDate: Int? // format YYYYMMDD
): List<Task>

Conclusion

So here’s what we have: we have a single query tied to a single DAO function, which allows us to combine multiple filters on a table of data in our database. It avoids a combinatoric explosion of functions and associated branching logic. I can’t promise this technique will work for all use cases; it depends on how the data is actually stored in your app and what those different filtering use cases are. But if you are looking to reduce the number of queries necessary to support all of the filters you provide for your data, this is one technique you can consider.