in clause with anorm

[David Galichet]

Hi all,

is there a way to use some sql "IN" clauses with anorm ?

I've tried something like :
User.find("id in {userIds}").on("userIds" -> List(1, 2, 3)).list()

I've also tried List(1, 2, 3).mkString(", ") unsuccessfully.

Regards,

David.

[Ivan Meredith]

SQL("select * from User where id in {userIds}").on ... ?

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

[Ivan Meredith]

well even that probably wont work, since List probabyl wont serialize.

[Amir Laher]

Are you forgetting SQL brackets?
 User.find("id in ({userIds})")

[David Galichet]

Ok I've found a way to achieve it :
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})".format(paramsList.mkString("},{")) //
produces "id in ({userId0},{userId1},{userId2})"
.on(paramsList.zip(params)) // produces Map("userId0" -> 1,
"userId1" -> 2, ...)
.list()

[Freewind]

This looks so complex

[Jay Taylor]

I agree, this seems like an overly intense solution.  Would love to see another solution posted here or at the StackOverflow question by Freewind: http://stackoverflow.com/questions/9528273/in-clause-in-anorm

[Matthew Barlocker]

Just posted my answer to http://stackoverflow.com/questions/9528273/in-clause-in-anorm/16071055#16071055

Posting it here as well:

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)

[adrian hurtado]

Hi to all, I published yesterday a tip for using custom string interpolation for Anorm that can be used for IN clause.

You could simply write, for example:

val userIds = List(1, 3, 5)
SQLin"select * from users where id in ($userIds)".as(userParser *)

Please, check it at this other thread.

Adrián Hurtado

[Jorge Cue]

Hello everyone!,

Pleas found next a cipy if actual code I am using;

def findAllWithRole(roles: List[String]) = DB.withConnection { implicit c =>
  val s = roles.map(x => s"'$x'").mkString(", ")
  SQL(
    s"""
      |select distinct u.* from "user" u
      |  join user_role ur on (ur.user_id = u.id)
      |  join role r on (r.id = ur.role_id)
      | where r.authority in ($s)
      | order by u.username
    """.stripMargin).as(parser.*)
}

Regards.

[adrian hurtado]

Hi Jorge, please check the sections "Multi-value support" and "SQL queries using String Interpolation" within the Anorm documentation. You can accomplish your code with simply:

def findAllWithRole(roles: List[String]) = DB.withConnection { implicit c =>

  SQL"""

    select distinct u.* from "user" u
    join user_role ur on (ur.user_id = u.id)
    join role r on (r.id = ur.role_id)

    where r.authority in ($roles)
    order by u.username
    """.as(parser.*)
}