3

I have a query that takes Seq[Int] as it's argument (and performs filtering like WHERE x IN (...)), and I need to compile it since this query is failry complex. However, when I try the naive approach:

Compiled((xs: Set[Int]) => someQuery.filter(_.x inSet xs))

It fails with message that 

Computation of type Set[Int] => Query[SomeTable, SomeValue, Seq] cannot be compiled (as type C)

Can Slick compile queries that takes a sets of integer as parameters?

UPDATE: I use PostgreSQL as database, so it can be possible to use arrays instead of IN clause, but how?

Maxim
  • 1,209
  • 15
  • 28

1 Answers1

7

As for the PostgreSQL database, the solution is much simpler than I expected.

First of all, there is a need of special Slick driver for PostgreSQL that support arrays. It usually already included in projects that rely on PgSQL features, so there is no trouble at all. I use this driver.

The main idea is to replace plain SQL IN (...) clause which takes the same amount of bind parameters as the amount of items in list, and thus cannot be statically compiled by Slick with PgSQL-specific array operator x = ANY(arr), which takes only one parameter for the array. It's easy to do with code like this:

val compiledQuery = Compiled((x: Rep[List[Int]]) => query.filter(_.id === x.any))

This code will generate query like WHERE x = ANY(?) which will use only one parameter, so Slick will accept it for compilation.

Maxim
  • 1,209
  • 15
  • 28