Slick compile query with Set[Int] parameter

Question

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 · Accepted Answer · 2016-03-10 11:57:05Z

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.

answered Mar 10, 2016 at 11:57

Maxim

1,26915 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slick compile query with Set[Int] parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related