1

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!

Maverick2805
  • 188
  • 2
  • 9
  • Expressions are what you need for SQLite.swift to help it generate a SQL query. The result that the OP wants is for the query to get "AND HI_Time is not null" added to the end of an existing query. – Lou Franco Nov 27 '22 at 18:49

1 Answers1

0

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

Lou Franco
  • 87,846
  • 14
  • 132
  • 192
  • Thank you Lou, for your taking time and your response/guidance to the sources, where I could check on the topic. shouldn't the line 450 in Operators.swift allow me to compare the Expression with nil? I now get the warning "Cannot convert value of type 'Expression to expected argument type 'Expression' Has this to do with the appending to the query or am I simply too stupid to understand. I was able to make many rather complex queries, but here, I seem to get stuck with my non-pro development skills – Maverick2805 Nov 27 '22 at 22:31
  • Can you add code to your question to show what you have now and the declaration of dynamicFilter – Lou Franco Nov 28 '22 at 00:42
  • Have added the code what I have currently in the original question. Thanks so much for your help. – Maverick2805 Nov 28 '22 at 01:30
  • Of course, I am too stupid The initialization of the dynamic filter was wrong. with Fixing it with var dynamicFilter = Expression(value: true) it worked perfectly. Sorry for troubling you and wasting your time. But again, thanks very much for your help! – Maverick2805 Nov 28 '22 at 01:34