Filter with check against list of tuples

[mxl]

We are missing support for the following case.

Example syntax:

case class Person(id: Int, name: String, age: Int)

val tuples: Seq[(String, Int)] = Seq(("Foo", 1), ("Bar", 2))

ctx.run(quote[Person].filter(t => lift(tuples).contains((t.name, t.age))))

that for Postgres compiles to:

SELECT id, name, age 
FROM person t
WHERE (t.name, t.age) in (('Foo', 1), ('Bar', 2))

and for other databases that do not support such syntax to:

SELECT id, name, age 
FROM person t
WHERE t.name = 'Foo' AND t.age = 1 OR t.name = 'Bar' AND t.age = 2

@getquill/maintainers

[mxl]

Seems it's similar to https://github.com/getquill/quill/issues/615.

[fwbrasil]

workaround: https://scastie.scala-lang.org/qpdPCa2QQoODJ9Q3qfcquw

[jcoughlin9]

Looks like the workaround on Scastie is gone. Would you mind reposting it?

[IIVat]

Could you share a workaround again?

[sgrankin]

@jcoughlin9 A version that survived in my code: https://gist.github.com/sgrankin/1862c194c6544be10dceb4b19a1259f3

Also note that on mysql 5.6 that construction may result in table scans; I've been trying out a different construct https://gist.github.com/sgrankin/b6fe659aacb93e57b4483dbfc1377e49 but it has has a bad quill runtime for non-trivial input sizes. (Patches forthcoming once I had time to fully test them).

[sujeet100]

A tail recursive version of liftQuery. https://gist.github.com/sujeet100/74346963bb1281619a08f9960a46e24c

[lihaoyi-databricks]

I just hit this in a work project, hope someone can figure out a solution :)

[AvaPL]

The solution from @sujeet100 is indeed tail-recursive but still causes stack overflows because of ast evaluation for large lists. Here is my code that doesn't have this issue:

query[Person].filter(person => liftTuples(tuples).contains((person.firstName, person.lastName)))

private def liftTuples(tuples: List[(String, String)]): Quoted[Query[(String, String)]] =
  if (tuples.isEmpty) // ... WHERE (x, y) IN (NULL) - always yields empty list
    infix"NULL".as[Query[(String, String)]]
  else { // ... WHERE (x, y) IN ( ('a','b'), ('c','d'), ... )
    val sqlFragment = tuples.map(tuple => s"('${tuple._1}','${tuple._2}')").mkString(",")
    infix"#$sqlFragment".as[Query[(String, String)]]
  }

I didn't make it generic but I think it's easy to adjust to your needs.

⚠️ Important note: if tuple elements contain single quotes, you have to escape them yourself to avoid errors and/or SQL injection.

[pslaski]

@guizmaii I know it was long time ago, but have you considered to address this issue in the future?