How do I correctly compare Options members in Slick?

Question

I'm doing things with Addresses, and the member subpremise(apartment/condo #) causes retrieves to miss. I also have concerns about subpremise being a part of my unique index constraint, given it can be null.

Failure filter:

tableQuery.filter(c=> (c.longitude === r.longitude && c.latitude === r.latitude) ||
        (c.streetNumber === r.streetNumber && c.route === r.route && c.subpremise === r.subpremise && c.neighborhoodId === r.neighborhoodId))

Successful filter: (by removung subpremise)

tableQuery.filter(c=> (c.longitude === r.longitude && c.latitude === r.latitude) ||
            (c.streetNumber === r.streetNumber && c.route === r.route && c.neighborhoodId === r.neighborhoodId))

I've included the definitions below s.t. if there is another contributing factor that I've missed, hopefully it will be noticed.

case class Address(id:Option[Long],streetNumber:Short,route:String,subpremise:Option[String],neighborhoodId:Fk,latitude:Option[Double],longitude:Option[Double])

class Addresses(tag: Tag) extends Table[Address](tag, "addresses") with Logging {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def streetNumber = column[Short]("street_number")
  def route = column[String]("route",O.NotNull)
  def subpremise = column[Option[String]]("subpremise")
  def neighborhoodId = column[Long]("neighborhood",O.NotNull)
  def latitude = column[Option[Double]]("latitude")
  def longitude = column[Option[Double]]("longitude")

  //Constraints
  def idx = index("idx_streetnum_route_subpremise_neighborhood", (streetNumber,route,subpremise,neighborhoodId), unique = true)
  def gps = index("gps", (latitude,longitude), unique = true)

  //Foreign Key
  def neighborhood = foreignKey("NEIGHBORHOOD_FK", neighborhoodId, Neighborhoods.tableQuery)(_.id)

  def * = (id.?,streetNumber,route,subpremise,neighborhoodId,latitude,longitude) <> (Address.tupled,Address.unapply)
}

For future readers, I suggest you eliminate a bunch of this code (i.e., everything that's not directly relevant to the problem---all those extra fields). — Aaron Novstrup, Jun 24 '14 at 16:41

score 4 · Answer 1 · answered Jun 24 '14 at 16:10

4

The answer was to use the following check.

( //Option Scenario both are defined
  (c.subpremise.isDefined && r.subpremise.isDefined && c.subpremise === r.subpremise) ||
  //Option Scenario both are empty
  (c.subpremise.isEmpty && r.subpremise.isEmpty)
)

answered Jun 24 '14 at 16:10

jordan3

877
5
13

4

You can probably avoid checking `isDefined`: `c.optField === r.optField || (c.optField.isEmpty && r.optField.isEmpty)` – Aaron Novstrup Jun 24 '14 at 16:26
AaronNovstrup you are 100% right, I just verified/tested it. Thanks!! – jordan3 Jun 24 '14 at 16:34
3

An answer that explains *why* you need those seemingly redundant checks would be helpful. Hopefully a Slick/SQL guru will come along and provide such an answer, but I suspect that it has to do SQL's three-valued logic. An equality comparison in three-valued logic does not return `true` if either value is NULL. – Aaron Novstrup Jun 24 '14 at 16:45
5

That is it. To quote [this answer](https://stackoverflow.com/a/1724420/3404097): "WHERE x=y" in SQL actually means "WHERE x is not null and y is not null and x=y". – philipxy Jun 24 '14 at 21:04

Alex Elkin · Answer 2 · 2019-09-05T10:35:36.113

0

My solution that working with Slick 3.3.x

tableQuery
  .filterIf(r.subpremise.isEmpty)(_.subpremise.isEmpty) // 1. both are empty
  .filterOpt(r.subpremise)(_.subpremise === _) // 2. both are defined and equal

Case 1 (r.subpremise is empty) corresponds the following SQL:

select * from addresses where subpremise is null

Case 2 (r.subpremise isn't empty):

select * from addresses where subpremise = ?

edited Sep 05 '19 at 10:35

answered Sep 05 '19 at 10:03

Alex Elkin

574
6
11

How do I correctly compare Options members in Slick?

2 Answers2