Struggling with going full SharginGRDB in triggers

[sillygoose]

I have the following trigger that works but it bothers me I am using #sql() code. The concept is I have a table 'locations' that is referenced from the 'trips' and 'charges' tables and I want to delete a location when no trips or charges refer to it.

try Charge.createTemporaryTrigger(
      "hard_delete_location_after_charge_delete",
      after: .delete { old in
        Location
          .where { $0.id == old.locationID }
          .delete()
      }
      when: { old in
        #sql("(SELECT COUNT(*) FROM charges WHERE locationID = OLD.locationID) = 0")
      }
    )
    .execute(db)
  }

So I tried using

(Charge.count(filter: \.chargeLocationInCharge) == 0)

extension Charge.TableColumns {
  var chargeLocationInCharge: some QueryExpression<Bool> {
    #sql("locationID = OLD.locationID")
  }
}

Is there a way do this cleanly without the #sql() or just get over it and move on? The desired code would be

(Charge.count(filter: \.locationID == old.locationID) == 0)

but this isn't a QueryExpression so doesn't compile.

[mbrandonw]

Hi @sillygoose, you can construct your when like this:

when: { old in
  Charge
    .where { $0.locationID.eq(old.locationID) }
    .exists()
}

This has the added benefit of being a little more efficient too. SQLite can just stop once it finds a row satisfying the condition rather than counting all rows that satisfy the condition.

[sillygoose]

Thanks so much, concise and works great (adjusting for when no rows exist).

Now to spend some time with the created SQL code to see what happens under the hood.