Structured Query with an Optional

[StewartLynch]

If I leave out this optional Category in my ItemPropertyRow struct, my structured query works great and gives me exactly what I want for my ItemPropertyRow without my attempt on the leftJoin. However, if I want to include that optional category if it exists, because the Item which has an optional categoryID that joins to my Category table , it fails with the error:

Instance method 'eq' requires the types 'TableColumn<Category.TableColumns.QueryValue, Int>.QueryValue' (aka 'Int') and 'TableColumn<Item.TableColumns.QueryValue, Category.ID?>.QueryValue' (aka 'Optional<Int>') be equivalent

Can anyone help me with this?

 @Selection // Virtual table
 struct ItemPropertyRow {
     let item: Item
     let room: Room
     let property: Property
     let category: Category?
 }
 var itemPropertyRowsQuery: some StructuredQueries.Statement<ItemPropertyRow> {
     Item.where{
             for term in searchText.split(separator: " ") {
                 ($0.name.contains(term)
                  || $0.brand.contains(term)
                  || $0.description.contains(term))
             }
         }
         .join(Room.all) {
             $1.id.eq($0.roomID)
         }
         .join(Property.all) {
             $2.id.eq($1.propertyID) && $1.propertyID.eq(id!)
         }
         .leftJoin(Category.all) {
             $3.id.eq($0.categoryID)
         }
         .select {
             ItemPropertyRow.Columns(
                 item: $0,
                 room: $1,
                 property: $2,
                 category: $3
             )
         }
 }

[StewartLynch]

I am able tp get what I need by doing this for the left join because an id can never be -1

.leftJoin(Category.all) {
        $3.id.eq($0.categoryID ?? -1)
}

Is there a better way to write this? It seem kind of hacky to me.

[mbrandonw]

Hi @StewartLynch, this is an example where Swift's optional promotion cannot work, and so you are forced to deal with how to compare an optional and non-optional thing. And further, SQL has a 3-valued logic system (true, false, NULL), and in that system nothing is equal to NULL, even NULL itself. That can make things confusing, but what it means in practice is that when comparing things that can be NULL, you should use the "IS" operator instead of "=":

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL.

So for your join you would do:

.leftJoin(Category.all) {
  $3.id.is($0.categoryID)
}

And in general we (and SQL) recommends using the "IS" operator when needing to compare things that can be NULL.