“In” clause in anorm?

Question

It seems no easy way to use "in" clause in anorm:

val ids = List("111", "222", "333")
val users = SQL("select * from users where id in ({ids})").on('ids-> ???).as(parser *)

How to replace the ??? part?

I tried:

on('ids -> ids)
on('ids -> ids.mkString("'","','","'"))
on('ids -> ids.mkString("','")

But none works.

I see in the discussion the exactly same problem: https://groups.google.com/d/topic/play-framework/qls6dhhdayc/discussion, the author has a complex solution:

val params = List(1, 2, 3) 

val paramsList = for ( i <- 0 until params.size ) yield ("userId" + i) 

// ---> results in List("userId0", "userId1", "userId2") 

User.find("id in ({%s})"

    // produces "id in ({userId0},{userId1},{userId2})"
    .format(paramsList.mkString("},{")) 

    // produces Map("userId0" -> 1, "userId1" -> 2, ...) 
    .on(paramsList.zip(params))
    .list() 

This is too much complicated.

Is there any easier way? Or should play provide something to make it easier?

Answer 1

It's probably late, but I add this for others looking for the same. You could use some built-in database features to overcome this. This is one of the advantages Anorm has over ORMs. For example, if you are using PostgreSQL you could pass your list as an array and unnest the array in your query:

I assume ids are integer.

val ids = List(1, 2, 3)

val idsPgArray = "{%s}".format(ids.mkString(",")) //Outputs {1, 2, 3}

val users = SQL(
  """select * from users where id in (select unnest({idsPgArray}::integer[]))"""
).on('ids-> ???).as(parser *)

Executed query will be

select * from users where id in (select unnest('{1, 2, 3}'::integer[])) 

which is equal to

select * from users where id in (1, 2, 3)