CODEWITHSUNDEEP
javamysqlsqlscalaplayframework
Govind Singh
Govind Singh

Reputation: 15490

mysql query in clause at playframework

in below two sql query sql1 not selecting any row, and sql2 selecting only 1 for [email protected]

 var ids="'[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]'"

    val sql1 = SQL("SELECT id,point,privacy FROM `pointTable` WHERE state=1 and  id in ({users})").on("users" -> ids)
    sql1().map { row =>
      val point = if (row[Boolean]("privacy")) { row[Double]("point").toString } else { "0" }
      println(write(Map("id" -> row[String]("id"), "point" -> point)))
    }

    val sql2 = SQL("SELECT id,point,privacy FROM `pointTable` WHERE state=1 and  id in (" + ids + ")")
    sql2().map { row =>
      val point = if (row[Boolean]("privacy")) { row[Double]("point").toString } else { "0" }
      println(write(Map("id" -> row[String]("id"), "point" -> point)))
    }

in phpmyadmin when i run this query manualy it returns 6 rows then why not working perfectly here. i am using play framework 2.2 with scala 2.1

Upvotes: 0

Views: 200

Answers (1)

Michael Zajac
Michael Zajac

Reputation: 55569

That's not going to work. Passing users though on is going to escape the entire string, so it's going to appear as one value instead of a list. Anorm in Play 2.3 actually allows you to pass lists as parameters, but here you'll have to work around that.

val ids: List[String] =  List("[email protected]", "[email protected]", "[email protected]")
val indexedIds: List[(String, Int)] = ids.zipWithIndex

// Create a bunch of parameter tokens for the IN clause.. {id_0}, {id_1}, ..
val tokens: String = indexedIds.map{ case (id, index) => s"{id_${index}}" }.mkString(", ")

// Create the parameter bindings for the tokens
val parameters = indexedIds.map{ case (id, index) => (s"id_${index}" -> toParameterValue(id)) }

val sql1 = SQL(s"SELECT id,point,privacy FROM `pointTable` WHERE state=1 and  id in (${tokens})")
    .on(parameters: _ *)

Upvotes: 1

Related Questions