Is there a conditional where statement in SQLite?

Question

I have a Todo application with a database for the todos. It contains a column done which will be set to false or true. Now I want to filter the data by all or unfinished. So either done is irrelevant or it has to be false.

I am using SQLite3 in a TypeScript application. Currently I do this by using string templates but I'd prefer an SQL-based solution.

db.prepare(`
  select 
    rowid as id, 
    title, 
    description
  from todo
    ${selectAll ? '' : 'where done = 0'}
    limit ?
    offset ?
`).all(limit, offset);

My idea was to use the CASE clause but it seems not to work around the WHERE clause.

Is there any better solution?

selectAll is a TypeScript variable that is set depending on the query parameters of the app.

Output when selectAll is false

id | title             | description          | done
1  | Clean the kitchen | as the title says... | 1
2  | Do the shopping   | potatoes, tomatoes   | 0
3  | Program stuff     | Todo app             | 1

Output when selectAll is true

id | title             | description          | done
2  | Do the shopping   | potatoes, tomatoes   | 0

Answer 1

You can use boolean logic:

where ? = '' or done = 0