"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?

Chouest

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 *)

Quintanilla

Can also use SQL"select * from users where id in ($ids)".as(parser.*) – Quintanilla

Note that in 2.3 this needs to be defined explicitly as a Seq - list.toSeq does not work (see this). – Comptroller

Note that 2.3 is quite old – Quintanilla

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)

Halitosis

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:_*)
    }
  }

}

Epizoon

it may be too late to answer this question but it was just in time to help me. works perfectly – Realpolitik

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)

Remanence

Answer 5

I had the same problem recently. Unfortunately there doesn't seem to be a way without using string interpolation and thus vulnerable to SQL injection.

What I ended up doing was kinda sanitizing it by transforming it to a list of ints and back:

val input = "1,2,3,4,5"

// here there will be an exception if someone is trying to sql-inject you
val list = (_ids.split(",") map Integer.parseInt).toList

// re-create the "in" string
SQL("select * from foo where foo.id in (%s)" format list.mkString(","))

Homogenous

Answer 6

User.find("id in (%s)"
  .format(params.map("'%s'".format(_)).mkString(",") )
  .list() 

Saddlebow

This solution is effectively the same as the one suggested in the question, with the one exception that it's vulnerable to SQL injection because you're pasting parameters directly into the query string (with no escaping!), rather than using bind parameters. Not worth it for the sake of saving 14 characters! – Laufer

A gaping security hole is not worth 14 characters?! – Antwanantwerp

Answer 7

val ids = List("111", "222", "333")
val users = SQL("select * from users 
                 where id in 
                 (" +  ids.reduceLeft((acc, s) => acc + "," + s) + ")").as(parser *)

Wast

It would be more idiomatic to write the reduceLeft part as simply ids.mkString(","). And as per the other answer, this is vulnerable to SQL Injection due to putting the parameters directly into the query string. – Laufer