Slick join queries with variable conditions

[Ryan LeCompte]

Hello all,

I'm recently playing with the latest version of Slick. I really like how easy it is to write join code using for expressions. However, I'm not sure how I can use this nice syntax if my "where" conditions are optional based on user input. For example, let's use the example that comes with Slick:

    val q2 = for {
      c <- Coffees if c.price < 9.0
      s <- Suppliers if s.id === c.supID
    } yield (c.name, s.name)

I'm not sure how I could inject a new condition in here dynamically. For example, let's say that at run-time I may decide to augment the above query with a new where condition, e.g.: s <- Suppliers if s.id === c.supID && s.name === "Ikea". Based on user input I may or may not want to include the "&& s.name === ..." part. Given that for expressions are compile-time syntax sugar, is there a way that I can express the above inner join and conditions without using the for-expression syntax? I tried something like the following, but it didn't generate the most efficient SQL:

Coffees.innerJoin(Suppliers).on { _.id === _.supID }. where { .... }.map { ... }.list()

The above code generated inefficient SQL code, whereas if I print out the q.selectStatement from the for-expression it definitely yields efficient SQL (i.e., what I would write if I were manually writing the SQL myself).

Any thoughts?

Thanks!

Ryan

[Christopher Vogt]

Hi Ryan,

look at how for-comprehensions are desugared in Scala.
http://www.artima.com/pins1ed/for-expressions-revisited.html#23.4

> val q2 = for {
> c <- Coffees if c.price < 9.0
> s <- Suppliers if s.id === c.supID
> } yield (c.name, s.name)

can be expressed using method syntax as:

Coffees.filter(c => c.price < 9.0 ).flatMap( c =>
Suppliers.filter(s => s.id === c.supID)
.map(s => (c.name,s.name))
)

Rhere are flags you can give to scalac to see the exact desugaring (e.g.
-Xprint:typer)

For you use case you can however also do this:

val q2 = for {
c <- Coffees if c.price < 9.0

s <- Suppliers if condition(c,s)
} yield (c.name, s.name)

def condition( c: Coffees, s:Suppliers ) = {
val baseCondition = s.id === c.supID
if( foo )
baseCondition && s.name === "Ikea"
else
baseCondition
}

Chris

[virtualeyes]

Interesting, do we lose prepared statement generation with the dynamic finder approach?

[Christopher Vogt]

> Interesting, do we lose prepared statement generation with the dynamic
> finder approach?

Slick always uses prepared statements internally. I suppose you mean
caching of precompiled queries (using the Parameters generator or
Compiled{} in Slick 2)? In that case you could cache them for each
different condition, if the number is in limits. Otherwise you are
probably out of luck and have to re-compile the query for every new
condition.

[virtualeyes]

Not a big deal, I don't often find a need for dynamic finders, usually parameterizing a query is enough.

Slick 2 caching of parameterized query snippets is looking pretty good from the ScalaQuery side of the fence, boilerplate begone.

[Ryan LeCompte]

Hey, thanks for Christopher! I am familiar with how for expressions
are desugared, I just wasn't sure if there was some macro that was
getting kicked in to make this transformation happen differently. I
really like your last suggestion which allows me to keep the sugared
for expression and also inject dynamic conditions on top of the base
condition.

Thanks!

Ryan

> --
>
> ---
> 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.
> For more options, visit https://groups.google.com/groups/opt_out.