[2.0] Anorm Magic and "in" clause

[Jun Yamog]

Hi,

I have searched about 2 emails regarding support for anorm's "in" sql
clause. But there seems to be no real conclusion the thread.

How can I make sql "in" clause work?

val listOfBars = List(1, 2, 3)
SQL("""select * from foo where bar in
({listOfBars})""").on("listOfBars" -> listOfBars)

Also it seems after 2.0 Beta Magic is not in anorm jar anymore? I was
using the jars published for anorm standalone here:

http://download.playframework.org/ivy-releases/play/anorm_2.9.1/2.0-beta/jars/

Then when I grabbed the anorm jar from a locally built Play RC-1
framework, I can seem to see Convention Magic anymore. Can someone
tell me where the Magic is now in 2.0? Thanks.

Jun

[Guillaume Bort]

For now we removed the Magic helpers in Anorm. It was giving the wrong
idea of Anorm.

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

[Drew Hamlett]

I like exactly where Anorm is at right now. It's not an ORM which
makes DB access more tricky than it has to be and it's not plain JDBC
which is a nightmare. It's right in the middle where DB access should
be. The whole ORM entity thing is a nightmare when getting into more
complex databases.

I actually don't mind writing my own row parsers(gives me tons of
control). I feel like I'm repeating myself sometimes but then I think
back to how bad it is to work with plain JDBC. I then look at some of
the stuff I've done with Hibernate and realize how much of a pain that
was too. I enjoy right plain queries, and while their not type safe,
it's easy, clean and I'm not using some specific API that generates
queries for me. I know exactly what is going to the database.

I'm not fiddling with annotations, detached entities, getters/setters,
etc.

Also, the Scala Option type is a perfect fit to DB access.

The only problem I have with Anorm at this point is it's dropping the
time off dates.

https://play.lighthouseapp.com/projects/82401/tickets/96-anorm-removes-time-from-date-being-passed#ticket-96-1

Thanks and good luck.

On Jan 18, 12:27 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> For now we removed the Magic helpers in Anorm. It was giving the wrong
> idea of Anorm.
>
>
>
>
>
>
>
>
>
> On Wed, Jan 18, 2012 at 3:06 AM, Jun Yamog <jkya...@gmail.com> wrote:
> > Hi,
>
> > I have searched about 2 emails regarding support for anorm's "in" sql
> > clause.  But there seems to be no real conclusion the thread.
>
> > How can I make sql "in" clause work?
>
> > val listOfBars = List(1, 2, 3)
> > SQL("""select * from foo where bar in
> > ({listOfBars})""").on("listOfBars" -> listOfBars)
>
> > Also it seems after 2.0 Beta Magic is not in anorm jar anymore?  I was
> > using the jars published for anorm standalone here:
>

> >http://download.playframework.org/ivy-releases/play/anorm_2.9.1/2.0-b...

[Drew Hamlett]

For the IN do this.

Seq(3075, 3074, 18172).mkString(", ")

Always look for Scala functions before anything. There always seems
to be one to take on most tasks.

On Jan 17, 9:06 pm, Jun Yamog <jkya...@gmail.com> wrote:
> Hi,
>
> I have searched about 2 emails regarding support for anorm's "in" sql
> clause.  But there seems to be no real conclusion the thread.
>
> How can I make sql "in" clause work?
>
> val listOfBars = List(1, 2, 3)
> SQL("""select * from foo where bar in
> ({listOfBars})""").on("listOfBars" -> listOfBars)
>
> Also it seems after 2.0 Beta Magic is not in anorm jar anymore?  I was
> using the jars published for anorm standalone here:
>

> http://download.playframework.org/ivy-releases/play/anorm_2.9.1/2.0-b...

[Drew Hamlett]

Actually that won't work in that case. What I would do for now is
just use map.

val foos : Seq[Foo] = Seq(20,10,20).map (n => Foo.findById(n))

[Jun Yamog]

Hi,

Was just asking about Magic as it was on 1.2.x and beta 2.0, there are
a few tables where it could be handy. However if its really out in
2.x that is also ok w/ me. I primarily picked anorm because I wanted
to do sql. I am using it for a data extract code, so direct sql seems
to be a better option in the current context.

I have also used mkString, in particular mkString("'","','","'").
However it seems to be a bit weird. Some of my code currently does a
mkString on some condition and uses ".on" on some condition. Maybe
iterable type place holders should be expanded?

Jun

[ben]

On 18 Jan., 20:06, Drew Hamlett <drewhj...@gmail.com> wrote:

> The only problem I have with Anorm at this point is it's dropping the
> time off dates.

hi drew,

since i'm not a sql pro i have no idea if i'm messing things up with

-implicit val dateToStatement = new ToStatement[java.util.Date] {
- def set(s: java.sql.PreparedStatement, index: Int, aValue:
java.util.Date): Unit = s.setDate(index, new
java.sql.Date(aValue.getTime()))
- }

+ implicit val dateToStatement = new ToStatement[java.util.Date] {
+ def set(s: java.sql.PreparedStatement, index: Int, aValue:
java.util.Date): Unit = s.setTimesatmp(index, new
java.sql.Timestamp(aValue.getTime()))
+ }
in framework/src/anorm/src/main/scala/Anorm.scala

but at least it gives me times in dates.

cheers,

ben

[Drew Hamlett]

Hey Ben. Thanks for that info. I noticed this a few days ago when
looking at source.

https://play.lighthouseapp.com/projects/82401-play-20/tickets/96-anorm-removes-time-from-date-being-passed

I submitted a ticket for it. I'm wondering if we should do a pull
request to change it?

[Drew Hamlett]

Alright I sent a pull request. I just changed the sql.Date to
util.Date since sql.Date doesn't support time.

On Jan 19, 5:36 am, ben <b...@willers2000.de> wrote:

[Guillaume Bort]

It is not the good way to fix it. I think that Anorm is just missing
support for java.sql.Timestamp

> --
> 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.

[Drew Hamlett]

Hey Guillaume, what is the proposed fix at this point? Thanks

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

[Ivan Meredith]

I was using anorm, but recentlyish I switched to slick and like it a lot In times when I can't figure out how to construct the query with the dsl I just use it for direct sql. It supports string interpolation which is nice. Not sure how it handles IN's. although I believe the dsl can.

--

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

To unsubscribe from this group and stop receiving emails from it, send an email to play-framewor...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jun Yamog]

Matthew,

Thanks for sharing the code.  I think its the best answer for this problem.

[adrian hurtado]

Hi to all, I've just publish a tip that 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