Mapping Projection in "Join" Comprehension

[Chris DaMour]

I'm thinking this is a common enough case that there's some patterns out there for this...but i can't find them.

In our app we have some models that are summary objects.  Their unique characteristic is that they aren't stored in 1 table, but we use them in enough places that it makes sense to type them as a concrete model in our application.  Their data comes from joining a bunch of other DB models.   We've got it working, but the code is pretty ugly because right now the SLICK for comprehension yields a tuple which is then mapped into the summary model like this:

(for {

  t <- Tactics if t.brandId === request.user.brandId

  m <- Mediums if m.id === t.mediumId

  g <- Geographies if g.id === t.geographyId

  p <- Participants if p.id === t.participantId

  tar <- Targets if tar.id === t.targetId

} yield (t.id, t.name, m.name, g.name, p.name, tar.name, t.insertDate.?)

).list.map(

  t => TacticSummary(t._1, t._2, t._3, List(t._4, t._5, t._6).mkString(", "), t._7)

)

It's ugly because the tuple is getting pretty big and remember what each _N is is tricky.

I'd much rather have something like 

for {

  t <- Tactics if t.brandId === request.user.brandId

  m <- Mediums if m.id === t.mediumId

  g <- Geographies if g.id === t.geographyId

  p <- Participants if p.id === t.participantId

  tar <- Targets if tar.id === t.targetId

} yield TacticSummary(t.id.getResult, t.name, m.name, List(g.name, p.name, tar.name).mkString(", "), t.insertDate.?)

but obv that doesn't work.

I was thinking maybe a different * projection would make this cleaner..but i'm not sure how that would wire up.

anyone have any suggestions?

[virtualeyes]

Couple things:

1) I bet you've got foreign keys setup on those tables; you can reduce some of the noise:

for {

  t <- Tactics if t.brandId === request.user.brandId

  m <- t.mediums

  g <- t.geogrpahies

  p <- t.participants

  tar <- t.targets

}

2) request.user.brandId
likely means you're wrapping the query in a def, which will require Slick to regenerate the query on every request. If you bind the variable via Parameters or the new Composed equivalent, the query will be generated a single time. Small win, but hey, why not go lean and mean?

3) I call the aggregate instances you're referring to as projections. If you setup your projection case class to match expected query result you should be able to put those tuples to work with tupled:
q.list.map(TacticSummary.tupled)

and avoid the boilerplate entirely. If you're nesting case classes within your projection you may need to map{} through the collection and piece things together, in which case case is your friend:
q.list.map{case(t,m,g,p,tar)=> ...}

still in the stone ages here on ScalaQuery 0.10 but the above should apply regardless

[Chris DaMour]

hey thanks for the response.

1) You are right, the FK's made the if's superfluous, thanks for pointing that out!

2) not familiar with this, got a link handy for an example?

3) The List().mkString makes this a bit tricky, but i guess i could switch that up, but i'm still calling map. I was hoping there was a way to avoid this.

[Christopher Vogt]

> 2) request.user.brandId
> likely means you're wrapping the query in a def, which will require
> Slick to regenerate the query on every request. If you bind the variable
> via Parameters or the new Composed equivalent, the query will be
> generated a single time. Small win, but hey, why not go lean and mean?

def query(brandId: Column[Int]) =
for {
t <- Tactics if t.brandId === brandId

m <- t.mediums
g <- t.geogrpahies
p <- t.participants
tar <- t.targets

} ...

val compiledQuery = Compiled(query) // cache this somewhere
compiledQuery(request.user.brandId) // execute pre-compiled query

See http://slick.typesafe.com/doc/2.0.1-RC1/queries.html#compiled-queries

Chris

[Chris DaMour]

 ah, i should have mentioned, this is 1.0.1 good to know how that works in 2.x though.

[Christopher Vogt]

> (for {
> t <- Tactics if t.brandId === request.user.brandId
> m <- Mediums if m.id === t.mediumId
> g <- Geographies if g.id === t.geographyId
> p <- Participants if p.id === t.participantId
> tar <- Targets if tar.id === t.targetId
> } yield (t.id, t.name, m.name, g.name, p.name, tar.name, t.insertDate.?)
> ).list.map(
> t => TacticSummary(t._1, t._2, t._3, List(t._4, t._5,
> t._6).mkString(", "), t._7)
> )
>
> It's ugly because the tuple is getting pretty big and remember what each
> _N is is tricky.

How about doing the concatenation on the server side?

for ... yield (t.id, t.name, m.name,
g.name.asColumnOf[String] + ", " +
p.name.asColumnOf[String] + ", " +
tar.name.asColumnOf[String],
t.insertDate.?)
).list.map(TacticSummary.tupled)

Depending on your types, you may be able to leave out some or all of the
asColumnOf calls.

This breaks down, when you cannot express your computation as a
server-side operation. In that case the limits of Scala force you into
numeric column identifiers, instead of column names. There is research
going on in EPFL and other places (e.g. shapeless' record types) to
overcome this limitation somehow, but this is work in progress and it is
unclear if or how well it could be integrated with Slick.

Alternatively, you can what we call a "custom shape" to teach Slick
structured types other than the built-in tuples and hlist. You can use
this to allow using a custom case class within your query. Here is a
"quick" example, which I will probably polish for the 2.0.1 docs. It
requires advanced Slick and Scala knowledge to understand.


import scala.slick.lifted.{ProductNodeShape, Shape, ShapeLevel}

// A custom case class
case class BRow(a: Int, b: String)
case class BProjection(a: Column[Int], b: Column[String])

// A Shape that tells Slick about the mapping between BProjection and BRow
final class PairShape[Level <: ShapeLevel, M <: BProjection, U <: BRow,
P <: BProjection](val shapes: Seq[Shape[_, _, _, _]]) extends
MappedScalaProductShape[Level, Product, M, U, P] {
def buildValue(elems: IndexedSeq[Any]) = {
def toWhatever[T](t:Any) = t.asInstanceOf[T]
BProjection(toWhatever(elems(0)), toWhatever(elems(1)))
}
def copy(shapes: Seq[Shape[_, _, _, _]]) = new PairShape(shapes)
override def toMapped(v: Any) = {
val elems = v.asInstanceOf[Product].productIterator.toIndexedSeq
new BRow(elems(0).asInstanceOf[Int], elems(1).asInstanceOf[String])
}
}
implicit def pairShape[Level <: ShapeLevel, M1, M2, P1, P2] =
new PairShape[Level, BProjection, BRow, BProjection](Seq(

implicitly[Shape[ShapeLevel.Flat,Column[Int],Int,Column[Int]]],implicitly[Shape[ShapeLevel.Flat,Column[String],String,Column[String]]]
))

// Use it in a table definition
class B(tag: Tag) extends Table[BRow](tag, "shape_a") {
def id = column[Int]("id", O.PrimaryKey)
def s = column[String]("s")
def all = (id, s)
def * = BProjection(id, s)
}
val bs = TableQuery[B]
bs.ddl.create

// Insert data with the custom shape
bs += BRow(1, "a")
bs.map(_.all) += ((2, "c"))
bs += BRow(3, "b")

// Use it for returning data from a query
val q3 = bs
.map { case b => BProjection(b.id, (b.s ++ b.s)) }
.filter { case BProjection(id, _) => id =!= 1 }
.sortBy { case BProjection(_, ss) => ss }
//.map { case Pair(id, ss) => Pair(id, Pair(42 , ss)) }
assertEquals(Vector(BRow(3,"bb"), BRow(2,"cc")), q2.run)

Chris

[Christopher Vogt]

In 1.0.1:

val compiledQuery = // cache this somewhere
for {
brandId <- Parameters[Int]

t <- Tactics if t.brandId === brandId
m <- t.mediums
g <- t.geogrpahies
p <- t.participants
tar <- t.targets
} ...

[Chris DaMour]

thanks for all this detail.  I didn't quite grasp it all, but i'll bookmark it and revisit.