[2.0] Anorm support for prepared statements with "in"

[David Ross]

Hello,

I was wondering adding support for "in" is a reasonable feature for anorm. Something like this: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives

Specifically, if the api could generalize the annoyance of dynamically creating the multiple ?'s. Possible syntax would be:

SQL("SELECT * FROM my_table WHERE my_id IN {ids}").onList('ids -> List(1, 2, 3))

Behind the scenes, this would be the same as:

SQL("SELECT * FROM my_table WHERE my_id IN ({ids1}, {ids2}, {ids3}").on('id1 -> 1, 'id2 -> 2, 'id3 -> 3)

But would be flexible on changing the number of items in 'ids

Thoughts?

Thanks,

David

[Guillaume Bort]

What would be the common way to set this value using standard JDBC?

> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/play-framework/-/Tsg3AiJAGHsJ.
> 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.

--
Guillaume Bort

[David Ross]

There is no common way in JDBC. It seems from the stackoverflow link above that people tend to do one of two things:

1. Eschew prepared statements altogether and just build the string with the raw values.
2. Have a helper method on top of the prepared statement to dynamically create a (?, ?, ?) and fill as needed.

I propose that anorm provide a common way to do (2).

[David Ross]

Here is some sample code that does it:

   def inHelper(sql: String, key: String, values: List[String]) = {

        val toBeReplaced = "{%s}" format key

        val length = values.size

        val newKeys = (1 to length) map ("%s%d" format (key, _))

        val newKeysWithBraces = newKeys map ("{%s}" format _)

        val replacement = "(%s)" format (newKeysWithBraces mkString ",")

        val newSql = sql replace (toBeReplaced, replacement)

        val parameterValues: List[ParameterValue[String]] = values map (toParameterValue(_))

        val pairs = newKeys zip parameterValues 

        SQL(newSql) on (pairs: _*)

    }

It would be great if this was built into anorm though.

[David Ross]

Would a patch to anorm for this be accepted? Or is this not a desired-enough feature?

[Guillaume Bort]

Well I'm not sure to understand exactly what would the API look like
from the user point of view. Can you show an example first?

--
Guillaume Bort

[David Ross]

Sure.

Without api support:

SQL("SELECT * FROM users WHERE user_id in (" + List(1, 2, 3) + ")") // bad

SQL("SELECT * FROM users WHERE user_id in ({id1}, {id2}, {id3})").on('id1 -> 1, 'id2 -> 2, 'id3 ->3) // impossible to generalize if you don't know the number of ids

With the function I wrote above:

inHelper("SELECT * FROM users WHERE user_id in {ids}", 'ids -> List(1, 2, 3))

With support in anorm:

SQL("SELECT * FROM users WHERE user_id in {ids}").onList('ids -> List(1, 2, 3))

Thoughts?

Dave

[Guillaume Bort]

Well it would be great, but I'm not sure how to implement this.
SQL(...) compute a prepared statement and it is too late to modify the
SQL statement after that.

[David Ross]

Ah, I see. How about:

ExpandableSQL("SELECT * FROM users WHERE user_id in {ids}").expand('ids -> List(1, 2, 3))

or:

SQL("SELECT * FROM users WHERE user_ids in {ids}".expand('ids -> 3)).onList('ids -> List(1, 2, 3))

Now these aren't as nice as what I proposed above, and not a whole lot better than just having a helper function. I do think, however, it would be nice to have a common way to solve this problem, as it seems to be a popular issue (see stackoverflow, for example).

[kheraud]

+1

The first one with ExpandableSQL would be great.

To do so, I chose the "+ daList.mkString(...) +" stuff but as said, it's bad...

Thanks for the helper function.

>> >>>> > play-framework@googlegroups.com.

>> >>>> > To unsubscribe from this group, send email to

>> >>>> > play-framework+unsubscribe@googlegroups.com.

>> >>>> > For more options, visit this group at
>> >>>> > http://groups.google.com/group/play-framework?hl=en.
>> >>>>
>> >>>>
>> >>>>
>> >>>> --
>> >>>> Guillaume Bort
>> >>>>
>> >>>> --
>> >>>> You received this message because you are subscribed to the Google
>> >>>> Groups "play-framework" group.

>> >>>> To post to this group, send email to play-framework@googlegroups.com.

>> >>>> To unsubscribe from this group, send email to

>> >>>> play-framework+unsubscribe@googlegroups.com.

>> >>>> For more options, visit this group at
>> >>>> http://groups.google.com/group/play-framework?hl=en.
>> >>>>
>> >>>
>> >>
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups
>> > "play-framework" group.

>> > To post to this group, send email to play-framework@googlegroups.com.

>> > To unsubscribe from this group, send email to

>> > play-framework+unsubscribe@googlegroups.com.

>> > For more options, visit this group at
>> > http://groups.google.com/group/play-framework?hl=en.
>>
>>
>>
>> --
>> Guillaume Bort
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "play-framework" group.

>> To post to this group, send email to play-framework@googlegroups.com.

>> To unsubscribe from this group, send email to

>> play-framework+unsubscribe@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/play-framework?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.

> To post to this group, send email to play-framework@googlegroups.com.

> To unsubscribe from this group, send email to

> play-framework+unsubscribe@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/play-framework?hl=en.



--
Guillaume Bort

--
You received this message because you are subscribed to the Google Groups "play-framework" group.

To post to this group, send email to play-framework@googlegroups.com.
To unsubscribe from this group, send email to play-framework+unsubscribe@googlegroups.com.

[Jun Yamog]

Thanks David for looking at this. I currently just mkString my seq,
which is not ideal. Would be good to have as part of anorm.

I would go for your 2nd option.

SQL(...).onList(...)

Does that mean this would be possible?

SQL(...).onList(...).on(...).

Makes the syntax more part of SQL. However if that proves to be
difficult then I guess ExpandableSQL should be great.

Maybe we should just accept a Seq a much more generic collection
rather than a List. We are only concerned about ordering right?

.onSeq where Seq can be of type String, Integer, Byte, etc. (any of
the supported jdbc types)

>>> >> >>>> > 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.
>>> >> >>>>
>>> >> >>>>
>>> >> >>>>
>>> >> >>>> --
>>> >> >>>> Guillaume Bort
>>> >> >>>>
>>> >> >>>> --
>>> >> >>>> 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.
>>> >> >>>>
>>> >> >>>
>>> >> >>
>>> >> >
>>> >> > --
>>> >> > 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.
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Guillaume Bort
>>> >>
>>> >> --
>>> >> 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.
>>> >>
>>> >
>>> > --
>>> > 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.
>>>
>>>
>>>
>>> --
>>> Guillaume Bort
>>>
>>> --
>>> 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.
>>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.

> To view this discussion on the web visit

> https://groups.google.com/d/msg/play-framework/-/UBkCn9Z_0yAJ.
>
> To post to this group, send email to play-fr...@googlegroups.com.

> To unsubscribe from this group, send email to

> play-framewor...@googlegroups.com.

[scott clasen]

FYI https://github.com/twitter/querulous/ supports this, might look there for inspiration

Here's their usage  

    val users = queryEvaluator.select("SELECT * FROM users WHERE id IN (?) OR name = ?", List(1,2,3), "Jacques") { row =>

  new User(row.getInt("id"), row.getString("name"))
}

>>> >> >>>> > play-framework@googlegroups.com.

>>> >> >>>> > To unsubscribe from this group, send email to

>>> >> >>>> > play-framework+unsubscribe@googlegroups.com.

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

>>> >> >>>> play-framework@googlegroups.com.

>>> >> >>>> To unsubscribe from this group, send email to

>>> >> >>>> play-framework+unsubscribe@googlegroups.com.

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

>>> >> > play-framework@googlegroups.com.

>>> >> > To unsubscribe from this group, send email to

>>> >> > play-framework+unsubscribe@googlegroups.com.

>>> >> > For more options, visit this group at
>>> >> > http://groups.google.com/group/play-framework?hl=en.
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Guillaume Bort
>>> >>
>>> >> --
>>> >> You received this message because you are subscribed to the Google
>>> >> Groups
>>> >> "play-framework" group.

>>> >> To post to this group, send email to play-framework@googlegroups.com.

>>> >> To unsubscribe from this group, send email to

>>> >> play-framework+unsubscribe@googlegroups.com.

>>> >> For more options, visit this group at
>>> >> http://groups.google.com/group/play-framework?hl=en.
>>> >>
>>> >
>>> > --
>>> > You received this message because you are subscribed to the Google
>>> > Groups
>>> > "play-framework" group.

>>> > To post to this group, send email to play-framework@googlegroups.com.

>>> > To unsubscribe from this group, send email to

>>> > play-framework+unsubscribe@googlegroups.com.

>>> > For more options, visit this group at
>>> > http://groups.google.com/group/play-framework?hl=en.
>>>
>>>
>>>
>>> --
>>> Guillaume Bort
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "play-framework" group.

>>> To post to this group, send email to play-framework@googlegroups.com.

>>> To unsubscribe from this group, send email to

>>> play-framework+unsubscribe@googlegroups.com.

>>> For more options, visit this group at
>>> http://groups.google.com/group/play-framework?hl=en.
>>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/play-framework/-/UBkCn9Z_0yAJ.
>

> To post to this group, send email to play-framework@googlegroups.com.

> To unsubscribe from this group, send email to

> play-framework+unsubscribe@googlegroups.com.