Mixing Option[T] and T in a single query

[andersgb]

Version: 1.2.1 Module: quill-jdbc Database: mysql

Steps to reproduce the behavior

I want to query two tables where I match an Option value to a non-Option value. This is similar to #584, but here my Option value is not originating from outside the query (i.e. we cannot use lift(Option(value))).

case class Record(id: String, groupId: String)
case class Entity(recordGroup : Option[String])

val exampleId: String = "1234" 

ctx.run {
  query[Record]
    .filter(_.id == lift(exampleId))
    .map(r => r.groupId)
    .flatMap(groupId => query[Entity].filter(e => e.recordGroup == Option(groupId)))
}

Expected behavior

Something along the lines of this SQL query:

SELECT e.* FROM record r, entity e WHERE (r.id = ?) AND (e.recordGroup == r.groupId)

Actual behavior

Compilation error:

Error:(50, 13) Found the following free variables: scala.
Quotations can't reference values outside their scope directly. 
In order to bind runtime values to a quotation, please use the method `lift`.
Example: `def byName(n: String) = quote(query[Person].filter(_.name == lift(n)))`

Workaround

I can split the query into two:

ctx.run {
  query[Record]
    .filter(_.id == lift(exampleId))
    .map(r => r.groupId)
}.map(groupId =>
  ctx.run {
    query[Entity]
    .filter(e => e.recordGroup == lift(Option(groupId)))
  }
)

But that's not really a good solution. Any better workarounds? @getquill/maintainers

[zifeo]

Would the following work?

ctx.run {
query[Entity]
  .filter(e => query[Record]
    .filter(_.id == lift(exampleId))
    .map(_.groupId)
    .contains(e.recordGroup)
  )
}

ScalaFiddle

[andersgb]

@zifeo Thanks, that does the trick!

Dunno if formulating the query as I proposed should be supported, feel free to close this issue.

[mosyp]

@andersgb or even like this (produce query as in expected clause)

ctx.run {
    query[Record]
    .filter(_.id == lift(exampleId))
    .map(r => r.groupId)
    .flatMap(groupId => query[Entity].filter(_.recordGroup.exists(_ == groupId)))
  }
// SELECT x2.recordGroup FROM Record x1, Entity x2 WHERE (x1.id = ?) AND (x2.recordGroup = x1.groupId)

Consider using exists/forall for comparing raw values to options.

structure x == Option(y) (if x and y parts of queries) is not supported, @fwbrasil any ideas how to be with this issue, enhancement or won't do?

[andersgb]

@mentegy thanks, that's closer to how my original proposal. I did not know exists, forall and even contains on the Option could be used here.

Since x.contains(y) works, I don't see a need for x == Option(y). A more informative error message and/or documentation on this would be great, though.

[fwbrasil]

2.0.0-SNAPSHOT already fails with a better error message: https://github.com/getquill/quill/pull/904