constructing plain sql queries dynamically

[Jim Newsham]

Does slick 3.0 provide a way to compose plain sql queries dynamically?  I'd like to do something like the following, which would hopefully produce the appropriate prepared statement with positional parameters:

val query = sql"""select name from person"""

val where = sql"""where age >= ${age}"""

val query2 = query + where

Unfortunately, the above does not compile. :)

Thanks,

Jim

[Jim Newsham]

I found out that previous slick versions had a StaticQuery API which seems like it would work for me.  This is referred to in Slick 2.1 documentation here: http://slick.typesafe.com/doc/2.1.0/sql.html.  Mention of this API was removed from the 3.0 documentation, but I see that the classes still exist.  Unfortunately, I don't know how to convert from a StaticQuery to a DBIO action.  Is this possible?

Thanks,

Jim

[Nick T]

Hi Jim,

You can use string interpolation with the # operator like this:

val select = "select * from table"

val where = "where id > 5"

val s = sql"#$select #$where"

db.run(s.as[Blah])

[Jim Newsham]

Thanks Nick.  I'm aware of the string splicing feature, unfortunately that doesn't quite meet my requirements, for the following reasons:

(1) I need to be able to arbitrarily compose sql statements together, adding some arbitrary number of filters ("where x = y").  As shown in my initial post, slick doesn't allow me to add two sql statements together.

(2) Some of the filters involve timestamp comparisons. Rather than trying to convert the timestamp bounds into strings and possibly getting that wrong, it would be nice if I could just give those values to slick as positional parameters, since slick is good at that kind of thing.

I found out that I can use the StaticQuery API and use Slick's deprecated db.withSession { ... } API to invoke it.  This is working for me, but as soon as Slick removes that API I'll be screwed.  I really hope slick will continue to support an API for composing (combining) plain sql queries.

Thanks,

Jim

[Martin Mauch]

Hi Jim, did you find a solution for this?

I'm running into the same problem (need to inline a date into the query string, otherwise Postgres does not optimize the query properly)...

[Stefan Zeiger]

Inlining can be done trivially by using strings and splicing the final result into sql"#${...}" but it doesn't allow composition of query fragments with embedded bind variables. It should be possible to add this feature to the new API by allowing a SQLActionBuilder (produced by sql"..." before calling ".as") to be spliced in. I've created https://github.com/slick/slick/issues/1287 for this.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/b6a61e79-9e45-46a9-8fd3-2cfdd26c3448%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[Christian Schmitt]

Why another Issue it's the same as https://github.com/slick/slick/issues/1161 maybe my explanation isn't as good as yours though.

[Jim Newsham]

I'm glad to see a ticket was opened for this. Thanks!

Jim