"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

Anorm now supports such a case (and more) since 2.3: "Using multi-value parameter"

Back to initial example it gives:

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

Answer 2

Nailed it! There haven't really been any more updates on this thread, but it seems to still be relevant. Because of that, and because there isn't an answer, I thought I'd throw mine in for consideration.

Anorm doesn't support 'IN' clauses. I doubt they ever will. There's nothing you can do to make them work, I even read a post where anorm specifically took out those clauses because they made Anorm feel 'like an ORM'.

It's fairly easy, however, to wrap the SqlQuery in a short class that supports the IN clause, and then convert that class into a SqlQuery when needed.

Instead of pasting the code in here, because it gets a little long, here is the link to my blog, where I've posted the code and how to use it.

In clause with Anorm

Basically, when you have the code from my blog, your statements look like this:

RichSQL(""" SELECT * FROM users WHERE id IN ({userIds}) """).onList("userIds" -> userIds).toSQL.as(userParser *)(connection)

Answer 3

Maybe it's too late but here is a tip for using custom string interpolation that also works for solve the problem of IN clause.

I have implemented a helper class to define a string interpolation. You can see it below, and you can simply copy and paste, but first let's see how you can use it.

Instead of write something

SQL("select * from car where brand = {brand} and color = {color} and year = {year} order by name").on("brand" -> brand, "color" -> color, "year" -> year).as(Car.simple *)

You can simply write:

SQL"select * from car where brand = $brand and color = $color and year = $year order by name".as(Car.simple *)

So using string interpolation it's more concise and easier to read.

And for the case of using the IN clause, you can write:

val carIds = List(1, 3, 5)
SQLin"select * from car where id in ($carIds)".as(Car.simple *)

Or for your example:

val ids = List("111", "222", "333")
val users = SQLin"select * from users where id in ($ids)".as(parser *)

For more information about string interpolation, check this link

The code for this implicit class is the following:

package utils

object AnormHelpers {

  def wild (str: String) = "%" + str + "%"

  implicit class AnormHelper (val sc: StringContext) extends AnyVal {

    // SQL raw -> it simply create an anorm.Sql using string interpolation
    def SQLr (args: Any*) = {
      // Matches every argument to an arbitrary name -> ("p0", value0), ("p1", value1), ...
      val params = args.zipWithIndex.map(p => ("p"+p._2, p._1))
      // Regenerates the original query substituting each argument by its name with the brackets -> "select * from user where id = {p0}"
      val query = (sc.parts zip params).map{ case (s, p) => s + "{"+p._1+"}" }.mkString("") + sc.parts.last
      // Creates the anorm.Sql
      anorm.SQL(query).on( params.map(p => (p._1, anorm.toParameterValue(p._2))) :_*)
    }

    // SQL -> similar to SQLr but trimming any string value
    def SQL (args: Any*) = {
      val params = args.zipWithIndex.map {
        case (arg: String, index) => ("p"+index, arg.trim.replaceAll("\\s{2,}", " "))
        case (arg, index) => ("p"+index, arg)
      } 
      val query = (sc.parts zip params).map { case (s, p) => s + "{"+ p._1 + "}" }.mkString("") + sc.parts.last
      anorm.SQL(query).on( params.map(p => (p._1, anorm.toParameterValue(p._2))) :_*)
    }

    // SQL in clause -> similar to SQL but expanding Seq[Any] values separated by commas
    def SQLin (args: Any*) = {
      // Matches every argument to an arbitrary name -> ("p0", value0), ("p1", value1), ...
      val params = args.zipWithIndex.map {
        case (arg: String, index) => ("p"+index, arg.trim.replaceAll("\\s{2,}", " "))
        case (arg, index) => ("p"+index, arg)
      }
      // Expands the Seq[Any] values with their names -> ("p0", v0), ("p1_0", v1_item0), ("p1_1", v1_item1), ...
      val onParams = params.flatMap {
        case (name, values: Seq[Any]) => values.zipWithIndex.map(v => (name+"_"+v._2, anorm.toParameterValue(v._1)))
        case (name, value) => List((name, anorm.toParameterValue(value)))
      }
      // Regenerates the original query substituting each argument by its name expanding Seq[Any] values separated by commas
      val query = (sc.parts zip params).map {
        case (s, (name, values: Seq[Any])) => s + values.indices.map(name+"_"+_).mkString("{", "},{", "}")
        case (s, (name, value)) => s + "{"+name+"}"
      }.mkString("") + sc.parts.last
      // Creates the anorm.Sql
      anorm.SQL(query).on(onParams:_*)
    }
  }

}

Answer 4

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)