Slick - optionally including / omitting a large column

Question

Suppose I have a table with a number of small columns, and a large (say BLOB) column:

  case class Thing(id: Int, small1: String, small2: String, small3: String, large: String)

  class ThingMapping(tag: Tag) extends Table[Thing](tag, "things") {

    def id = column[Int]("id", O.PrimaryKey, O.NotNull, O.AutoInc)
    def small1 = column[String]("small1")
    def small2 = column[String]("small2")
    def small3 = column[String]("small3")
    def large = column[String]("large")

    def * = (id, small1, small2, small3, large) <> (Thing.tupled, Thing.unapply)

  }

Under some circumstances, I'd like to query the table for all the columns except the large column. In others, I'd like to include it. I prefer to use case classes rather than tuples.

Is there good pattern in Slick for doing this?

Options I've considered:

Having two mappings -- a "skinny" and "fat" mapping.
Splitting out the large column into a separate table, then joining it in if required.

Why not define `large` as `Option[String]` on the `Thing` case class and optional in the table definition and then add another selection `def` like `def allNoLarge = (id, small1, small2, small3)` where you don't select the `large` field from the db. Then you leave it up to the calling code to pick which selection `def` is used. — cmbaxter, Aug 27 '14 at 13:12
@cmbaxter -- at the moment, my calling code uses things like: `val things = TableQuery[ThingMapping]`. How would that fit in with having multiple selection defs in the mapping? — Matt R, Sep 01 '14 at 09:44
you can still use the `TableQuery` as the starting point, and then use the `.map` method on it to produce a new query that only selects certain columns. I'll add an answer showing this. — cmbaxter, Sep 01 '14 at 12:37

score -1 · Answer 1 · edited Sep 04 '14 at 15:58

I think what you need here is the map function on your TableQuery to allow you to select only a subset of fields. So something like this:

case class Thing(id: Int, small1: String, small2: String, small3: String, large: String)
case class LiteThing(id: Int, small1: String, small2: String, small3: String)
class ThingMapping(tag: Tag) extends Table[Thing](tag, "things") {
  def id = column[Int]("id", O.PrimaryKey, O.NotNull, O.AutoInc)
  def small1 = column[String]("small1")
  def small2 = column[String]("small2")
  def small3 = column[String]("small3")
  def large = column[String]("large")
  def * = (id, small1, small2, small3, large) <> (Thing.tupled, Thing.unapply)
}
val things = TableQuery[ThingMapping]

val liteThingQuery = things.map(t => LiteThing(t.id, t.small1, t.small2, t.small3))

So I added another case class called LiteThing that represents the subset of fields, excluding the large column. I then use map to create a new query that will not select that large field and it maps to a LiteThing. I have no compiled this, but I'm pretty sure this is the direction you want to go in. I got this from the Hello Slick Activator Template, in the section "Selecting Specific Columns" (after fully expanding the tutorial info).

You can play around with alternatives like

def small = (id, small1, small2, small3)
def * = (small, large)

or

def small = (id, small1, small2, small3)
def * = small ~ large <> (Thing.tupled, Thing.unapply)

And use

things.map(_.small)

Slick - optionally including / omitting a large column

1 Answers1

Linked