Dynamic Filter in Sqlite.swift to check if a column is not null-CodePudding

I need some help in understanding the following problem. I have the need for a dynamic filter (trying to not duplicate the queries for different scenarios).

I have already found that I can create a dynamicFilter = Expression<Bool>(value: true) and then adding the clauses to the filter. As described here

Now I have a database field defined as nullable and want to add a filter if a condition is given:

let hi_time = Expression<Int?>("HI_Time")
    
if condition {
   dynamicFilter = dynamicFilter && (hi_time != nil)
}

However I am getting a warning: Comparing non-optional value of type 'Expression<Int?> to 'nil' always returns true and thus that filter is skipped and not added to the query.

Now as I understand the Expression<Int?> is saying, that the column can contain NULL values.

How can I now append this NULL check to the query when condition X is fullfilled? Can I somehow add Plain SQL here? or do I have to make two separate Queries? The problem is, I will have some other NULL Checks to perform that only are needed if a specific condition is fulfilled.

Thanks so much for the help, it's much appreciated.

EDIT:

var dynamicFilter = Expression<Bool>(value: true)

if cond.temperature.currentKind == .celsius {
            dynamicFilter = dynamicFilter && (upperTemp_C >= cond.temperature.getCurrentTemperature() && lowerTemp_C <= cond.temperature.getCurrentTemperature())
        } else {
            dynamicFilter = dynamicFilter && (upperTemp_F >= cond.temperature.getCurrentTemperature() && lowerTemp_F <= cond.temperature.getCurrentTemperature())
        }

if condition.rawValue == 1 {
            dynamicFilter = dynamicFilter && (hi_time !== nil)
        }


do {
        let numberOfHOT = try db.scalar(times
            .filter((delDate > Date() || delDate == nil))
            .filter(dynamicFilter)

...

SOLUTION:

With the help of Lou Franco the error was in the declaration of the dynamic filter. var dynamicFilter = Expression<Bool?>(value: true) did the trick.

Thanks so much!

CodePudding user response：

To check for inequality between an expression and an optional value with SQLite.swift, you need !==, not !=

Here is the relevant code:

https://github.com/stephencelis/SQLite.swift/blob/master/Sources/SQLite/Typed/Operators.swift#L450

You can also see this in the tests:

https://github.com/stephencelis/SQLite.swift/blob/master/Tests/SQLiteTests/Typed/OperatorsTests.swift#L214

When you don't find something in the docs, the tests in this project are a good place to look for answers