Sqldelight: Filtering in select queries

[aegis123]

What would be a best way to filter over 1 or more columns? Since we don't have a query builder object to dynamically create the select query, do we have to write a select query for every possible filter combination that we and then select the correct query. Or would making a select query with big where clause with or's be a better approach?

[AlecStrong]

can you give an example? I think I know what you're talking about but just want to make sure

[aegis123]

It gets a little verbose when you have write all combination of filter query. But if you write one big where or query you have to specify every parameter and that can change results.

So is there a better way of doing this.

import java.lang.Boolean;
import java.util.Calendar;

CREATE TABLE IF NOT EXISTS message (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    sendAt INTEGER AS Calendar NOT NULL,
    read INTEGER AS Boolean DEFAULT 0 NOT NULL,
    hasAttachments INTEGER AS Boolean DEFAULT 0 NOT NULL,
    archived INTEGER AS Boolean DEFAULT 0 NOT NULL,
    body TEXT NOT NULL
);

select_filter_read:
SELECT *
FROM message
WHERE read =?;

select_filter_attachment:
SELECT *
FROM message
WHERE hasAttachments =?;

....
etc for every combination of filter you want.

select_filter:
SELECT *
FROM message
WHERE read =?
OR hasAttachment=?
OR archived=?;

[AlecStrong]

yea i've thought about this problem before and haven't settled on a good solution.

If you include an OR for each clause there is potential for returning bad results since something like null could be meaningful if the column is nullable. Writing separate queries is a lot of boilerplate, as you mentioned, and especially when the non-filter parts of your query get more complex (like adding an ORDER BY) then you're pretty much forced to make a view which all the queries share.

Another alternative is including an extra variable for each filter saying whether that filter is applied:

selectFilter:
SELECT *
FROM message
WHERE (:shouldCheckRead = 1 AND read =?)
OR (:shouldCheckHasAttachment = 1 AND hasAttachment=?)
OR (:shouldCheckArchived = 1 AND archived=?);

which generates this method:

public SqlDelightStatement selectFilter(long shouldCheckRead, boolean read,
    long shouldCheckHasAttachment, boolean hasAttachment, long shouldCheckArchived,
    boolean archived);

it solves the "meaningful null" problem but it still sort of sucks because now you still have to pass a value for an attribute you're not checking, and those "shouldCheck" parameters should really be booleans but SQLDelight doesn't let you do that yet.

This is one _advantage_ of sorts that writing SQL in strings has. You can build a query as a series of parts:

StringBuilder query = new StringBuilder("SELECT * FROM table\n");
if (shouldCheckRead) {
  query.append("WHERE read = ?\n");
} else if (shouldCheckHasAttachment) {
  query.append("WHERE hasAttachment = ?\n");
}

if (orderById) {
  query.append("ORDER BY _id\n");
} else if (orderBySendAt) {
  query.append("ORDER BY sendAt\n");
}

This gets particularly ugly in SQLDelight because now you would need 9 sql queries to do the same thing entirely statically:

SELECT *
FROM table;

SELECT *
FROM table
WHERE read = ?;

SELECT *
FROM table
WHERE hasAttachment = ?;

SELECT *
FROM table
ORDER BY _id;

SELECT *
FROM table
ORDER BY sendAt;

SELECT *
FROM table
WHERE read = ?
ORDER BY _id;

SELECT *
FROM table
WHERE read = ?
ORDER BY sendAt;

SELECT *
FROM table
WHERE hasAttachment = ?
ORDER BY _id;

SELECT *
FROM table
WHERE hasAttachment = ?
ORDER BY sendAt;

and then you have to write java which checks the same predicates and selects the right query. And each time you want to add a new filter or order by you're adding another 3 queries at minimum.

The way I see it there are a few things that can be done:

1. Write a ton of boilerplate (above)
2. Write a ton of parameters (still boilerplate-y but easier to build off of)
3. Some existing way I haven't thought of
4. Add a feature to SQLDelight.

Ideally the answer is 3 because I cannot see a simple feature that would solve this problem in SQLDelight. The goal is to keep what you're writing in .sq files exact SQLite without compromise unless absolutely necessary (such as providing the java type in a column definition).

So I'm not sure! I'm all ears if anyone has any ideas because I haven't thought of anything yet and I've seen this in a lot of code - it needs a solution.

[AlecStrong]

Going to close this and track any runtime filtering related feature in https://github.com/square/sqldelight/issues/626