I can't work out how to handle optional field equality after a leftJoin

[ajaxharg]

I've been going in circles for a few days and would greatly appreciate some help here. I have the following tables

@Table
struct Hive {
 let id : UUID
 var name: String
var apiary: Apiary.ID
}

@Table
struct Queen {
 let id : UUID
 var name : String
}

@Table
struct QueenLocation {
 let id : UUID
 var queenID : Queen.ID
 var hiveID: Hive.ID
var date: Date
}

@Table
struct QueenMilestone: Identifiable {
    let id : UUID
    var date : Date
    var queenID: Queen.ID
    var milestone: Milestone
    enum Milestone: Int, QueryBindable {
        case egg
        case larvae
        case capped
        case virgin
        case mated
        case deceased
        case lost
    }
}

Queens have the location changed in the queenLocation table so that I have history. The entry with the newest date is the current location.
Milestones follows a similar logic.
I did want an optional hiveID in the queen location but couldn't work out how to write queries against it. I kept running into optional vs non optional not allowed in the eq.

I want a query that fills the following @selection with the hive and count of queens which must be in the hive and also not be dead.

@Selection
struct {
 var hives:Hive
 var count: Int
}

I have taken two approaches, but have run into showstoppers with both. I could do completely separate queries, but as mentioned in the videos, why do two when you can do one. I still don't know when I can't achieve something with one query, but this feels like it should be possible.

Firstly I did the following

let base = Hives.group(by: \.id)
                .where { h in h.apiaryID.eq(apiaryID)}                       
                .join(QueenLocation.all) { h, ql in h.id.eq( ql.locationID) } // 2) Join latest known locations of queens to housings
                .join(Queen.all) { _,ql, q in ql.queenID.eq( q.id) }   // 3) Join queens from those locations
                .join(QueenMilestone.all) {_,_, q, qm in q.id.eq(qm.queenID) }           // 4) Join milestones for those queens
              // 6) Keep only latest location per queen        
               .where { _ , ql , q , _ in
                        ql.date.eq(
                            QueenLocation.select{ $0.date.max() }
                                .where { $0.queenID.eq(q.id )
                                }
                            ?? Date.distantPast
                        )
                }
        
                // 7) Keep only latest milestone per queen
                .where { _, _, q, qm in
                    qm.date.eq( QueenMilestone
                        .select{ $0.date.max() }
                        .where { $0.queenID.eq(q.id )
                        }
                                ?? Date.distantPast)
                }
            // 8) Exclude deceased/lost queens
              .where { _, _, _, qm in
                    qm.milestone != QueenMilestone.Milestone.deceased &&
                    qm.milestone != QueenMilestone.Milestone.lost
                }
        
                //         10) Feed the staged query into the @FetchAll for colonies
                _colonies = FetchAll(
                    aliveQueens
                        .select { h,ql,q,m in
                        ColonyRow.Columns(
                            hives: h,
                            graftBarCount: 0,
                            queenCount: q.count()
                        )
                    }.distinct()
                )

Initially I struggled with the dealing with the optional at step 6,7 but used the ?? operator.

However, the query as it stands, only returns hives with queens in because the joins drop the hives with no queen entry. As soon as I put left joins in, I end up with step 7 not able to compare and optional id with an id. I understand why it is optional, but I can't seem to work out what to do.

.where { _ , ql , q , _ in
                        ql.date.eq(
                            QueenLocation.select{ $0.date.max() }
                                **.where { $0.queenID.eq(q.id )**
                                }
                            ?? Date.distantPast
                        )
                }
        

......ApiaryDetailView.swift:119:53 Instance method 'eq' requires the types 'TableColumn<QueenLocation.TableColumns.QueryValue, Queen.ID>.QueryValue' (aka 'UUID') and 'TableColumn<Optional, UUID?>.QueryValue' (aka 'Optional') be equivalent

So my second method which I dont even know if it is valid sql won't compile because the type checking takes too long. I have no idea how to split it up. It is dynamic on the apiaryID.

        let hives = Hives.all
                        .where{ $0.id.eq(apiaryID) }
                        .group(by: \.id)

_colonies=FetchAll(
            hives
                .select { h in
                    ColonyRow.Columns(
                        housing: h,
                        graftBarCount: 0,
                        queenCount: Queen.join(QueenLocation.all) { q,ql in
                            ql.queenID.eq(q.id) &&
                            ql.date.eq(QueenLocation
                                .select { $0.date.max() }
                                .where { $0.queenID.eq(q.id) } ?? Date.distantPast
                            )
                            && ql.locationID.eq(h.id)
                        }
                            .select{$0.count()}
                    )
                }
            )

I am fairly new to the whole sql way of the world outside of simple queries. Has anyone got any pointers please?

[ajaxharg]

Well, after much time throwing stuff at the wall, I found that moving the where clause from 6 and 7 in to their relevant joins and swapping the .eq order it fixed it. I have absolutely no idea why :)

The changed bit of code.

let joinedQueens = joinedLocations.leftJoin(Queen.all) { _,ql, q in 
           ql.queenID.eq( q.id)   && 
           ql.date.eq(
                QueenLocation.select{ $0.date.max() }
                    .where { $0.queenID.eq(q.id )
                    }
                ?? Date.distantPast)
        }
        
        // 4) Join milestones for those queens
        let joinedMilestones = joinedQueens.leftJoin(QueenMilestone.all) {_,_, q, qm in 
            q.id.eq(qm.queenID) &&
            qm.date.eq( QueenMilestone
                .select{ $0.date.max() }
                .where { q.id.eq($0.queenID)
                }
                        ?? Date.distantPast)
            
        }
        

[mbrandonw]

Hi @ajaxharg, at the end of the day you need to keep in mind that type-safety and schema-safety in queries is only aspirational, and there will always be limitations to the tools. And the more complex the SQL query, the more difficult it will be to have a faithful 1-to-1 representation of it using our query builder. However, there are number of things to keep in mind that can help you still create very complex queries without running into these build errors or having the Swift compiler just give up. A lot of this is covered in the docs so please be sure to read that.

The biggest thing you can do in your query is not use the heavily overloaded operators such as == and !=. I see you have used .eq everywhere, but the same goes for .neq and !=.

Another trick you can employ is to break down very large queries into smaller pieces. For example, you have this where clause that only depends on QueenMilestone:

// 8) Exclude deceased/lost queens
.where { _, _, _, qm in
  qm.milestone != QueenMilestone.Milestone.deceased &&
  qm.milestone != QueenMilestone.Milestone.lost
}

So you could have put that into a helper:

extension QueenMileston {
  static let notLostOrDeceased = Self.where {
    $0.milestone.neq(QueenMilestone.Milestone.deceased) &&
      $0.milestone.neq(QueenMilestone.Milestone.lost)
  }

Then one of your joins can be simplified to:

join(QueenMilestone.notLostOrDeceased) {_,_, q, qm in q.id.eq(qm.queenID) }   

Next, queries like this:

.where { _ , ql , q , _ in
  ql.date.eq(
    QueenLocation.select{ $0.date.max() }
      .where { $0.queenID.eq(q.id ) }
      ?? Date.distantPast
  )
}

…are extremely complex for the compiler and so would benefit breaking it out into helpers. But also, this is just very inefficient SQL. Performing a subquery that is dependent on a result being queried for is likely to be a very slow query. You could restructure both wheres using subqueries to use common table expressions (or even temporary views if you perform this kind of query often), which would allow these queries to be more efficient and would help break the query down into simpler pieces that would be easier to compile.

Also, the ?? operator is another one of those heavily overloaded operators that should probably be avoided when you have very complex queries like this. You can use .ifnull(…) instead.

And finally, you should always consider a localized use of the #sql escape hatch to further help the compiler. By using #sql you take a bit more responsibility for the correctness of the query, but it gives you full access to SQL with none of the type-checking burdens:

.where { _ , ql , q , _ in
  ql.date.eq(
    #sql("""
    SELECT max(\(QueenLocation.date))
    FROM \(QueenLocation.self)
    WHERE \(QueenLocation.queenID) = \(q.id)
    """)
  )
}

[ajaxharg]

Hi Brandon, thanks for taking the time to answer. It's been a bit of a journey jumping beyond the simpler examples :) It's slowly coming together. I spent some time in the docs, but it will take some time for me to absorb it all. Unfortunately, autocomplete has been non-existent for me on my computer (m1 16gb) which makes it pretty difficult. Same for type inference with option click. I'm assuming there isn't enough memory to hold everything and the indexing wheel spends its life spinning.

I'll go and do some study on the subqueries and have a play with the #sql operator.

Cheers
Andy

[ajaxharg]

For completeness I've posted my CTE solution for others. My optional issues stemmed from the order I did the equality check after a join. I thought it wouldn't matter which way round they are positioned, but it does. For example after the last QueenMilestone join, if I swap $2.mdate.eq($3.date) to $3.date.eq($2.mdate), I get the optional issue.

extension QueenMilestone{
      static let notDead = Self.where {
        $0.milestone.neq(QueenMilestone.Milestone.deceased) &&
          $0.milestone.neq(QueenMilestone.Milestone.lost)
      }
}
// Query to populate `_colonies` — step 1: Build a CTE with the latest dates per queen.
        //
        // For each queen, we compute two reference timestamps:
        // - mdate: MAX(QueenLocation.date)  => the most recent time we know where the queen was located
        // - ldate: MAX(QueenMilestone.date) => the most recent status/milestone recorded for the queen
        //
        // We'll use these two values in the main query to only join the "latest" records for a queen.
        let latestQueenDatesCTE = QueenLocation
            .group(by: \.queenID) // operate per queen
            .join(QueenMilestone.all) { $0.queenID.eq($1.queenID) } // pair locations and milestones for the same queen
            .select {
                CurrentLocationMilestoneDate.Columns(
                    queenID: $0.queenID,
                    mdate: #sql("MAX(\($0.date))"), // latest location date for the queen
                    ldate: #sql("MAX(\($1.date))")  // latest milestone date for the queen
                )
            }
        
        // Query to populate `_colonies` — step 2: Start from housings in this apiary and join latest info.
        //
        // We list all queen housings in the given apiary, then join the queen's latest location/milestone
        // so we can count queens that are currently not deceased or lost in each housing.
        let housingsWithLiveColonies = QueenHousing
            .where { $0.apiaryID.eq(apiaryID) } // limit to a single apiary
            .group(by: \.id) // one row per housing (hive, nuc, etc.)
            // Link housings to queen location records that reference this housing.
            .leftJoin(QueenLocation.all) { $0.id.eq($1.locationID) }
            // Join to the CTE so we can restrict to the queen's latest milestone date.
            // This effectively picks the location record that coincides with the latest milestone for that queen.
            .leftJoin(CurrentLocationMilestoneDate.all) { $1.queenID.eq($2.queenID) && $1.date.eq($2.ldate) }
            // Bring in the milestone that coincides with the queen's latest known location,
            // and filter out queens that are deceased or lost.
            .leftJoin(QueenMilestone.notDead) {
                // Match the queen and the latest location date; exclude 'deceased' and 'lost' states.
                $1.queenID.eq($3.queenID) && $2.mdate.eq($3.date)
            }
        
        
        // Query to populate `_colonies` — step 3: Execute with the CTE and build rows for the UI.
        _colonies = FetchAll(
            With {
                latestQueenDatesCTE // exposes `CurrentLocationMilestoneDate` to the main query
            } query: {
                housingsWithLiveColonies
                    .select { qh, ql, _, qm in
                        ColonyRow.Columns(
                            housing: qh,
                            graftBarCount: 0,        // TODO: Replace with a real count of graft bars when available.
                            queenCount: qm.count()
                        )
                    }
                    .distinct() // avoid duplicate rows caused by joins
            }
        )

[mbrandonw]

My optional issues stemmed from the order I did the equality check after a join. I thought it wouldn't matter which way round they are positioned, but it does. For example after the last QueenMilestone join, if I swap $2.mdate.eq($3.date) to $3.date.eq($2.mdate), I get the optional issue.

Typically it is best to use the SQL is operator for checking equality between things that are optional so that it will probably handle NULL. This is because in SQL, 1 = NULL evaluates to NULL.

[ajaxharg]

Thanks, it's exactly what I needed. I seem to generate an optional on one side of tests quite often and missed this one when looking through the operators.