Custom column (Object) type in Slick

Question

it seems that I can't find anywhere how to properly use custom column types in Slick and I've been struggling for a while. Slick documentation suggests MappedColumnType but I found it useable only for simple use-cases like primitive type wrappers (or it's probably just me not knowing how to use it properly).

Let's say that I have Jobs table in my DB described by JobsTableDef class. In that table, I have columns companyId and responsibleUserId which are Foreign keys for Company and User objects in their respective tables (CompaniesTableDef, UsersTableDef).

class JobsTableDef(tag: Tag) extends Table[Job] (tag, "jobs") {

  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def title = column[String]("title")
  def companyId = column[Long]("companyId")
  def responsibleUserId = column[Long]("responsibleUserId")

  def companyFK = foreignKey("COMPANY_ID_FK", companyId, companies)(i => i.id)
  def responsibleUserFK = foreignKey("RESPONSIBLE_USER_FK", responsibleUserId, users)(i => i.id)

  val companies = TableQuery[CompaniesTableDef]
  val users = TableQuery[UsersTableDef]

  override def * = (id, title, companyId, responsibleUserId) <> (Job.tupled, Job.unapply)
}

class CompaniesTableDef(tag: Tag) extends Table[Company] (tag, "companies") {

  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def name = column[String]("name")
  def about = column[String]("about")

  override def * = (id, name, about) <> (Company.tupled, Company.unapply)
}

class UsersTableDef(tag: Tag) extends Table[User] (tag, "users"){

  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def username = column[String]("username", O.Unique)

  override def * = (id, username) <> (User.tupled, User.unapply)
}

What I would like to achieve is to automatically 'deserialize' Company and User represented by their IDs in Jobs table. For example:

class JobsTableDef(tag: Tag) extends Table[Job] (tag, "jobs") {

  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def title = column[String]("title")
  def company = column[Company]("companyId")
  def responsibleUser = column[User]("responsibleUserId")

  def companyFK = foreignKey("COMPANY_ID_FK", companyId, companies)(i => i.id.?)
  def responsibleUserFK = foreignKey("RESPONSIBLE_USER_FK", responsibleUserId, users)(i => i.id.?)

  val companies = TableQuery[CompaniesTableDef]
  val users = TableQuery[UsersTableDef]

  override def * = (id, title, company, responsibleUser) <> (Job.tupled, Job.unapply)
}

given that my Job class is defined like this:

case class Job(
    id: Long,
    title: String,
    company: Company,
    responsibleUser: User,
)

Currently, I'm doing it in old-fashioned way of getting Job from the DB, reading companyId and responsibleUserId, then querying the DB again and manually constructing another Job object (of course, I could also join tables, get the data as tuple and then construct Job object). I seriously doubt that this is the way go. Is there a smarter and more elegant way to instruct Slick to automagically fetch linked objects from another tables?

EDIT: I'm using Play 2.6.12 with Slick 3.2.2

I think joining table and construct the object is the better way of doing this. And there is no other smarter way. — tomcy

bpervan bpervan · Accepted Answer · 2018-04-04T14:43:45

After a couple of days of deeper investigation, I've concluded that it's currently impossible in Slick. What I was looking for could be described as auto joining tables described through custom column types. Slick indeed supports custom column types (embodied through MappedColumnType, as described in docs) but it works only for relatively simple types which aren't composed of another objects deserialized from DB (at least automatically, you could always try to fetch another object from the DB and then Await.result() the resulting Future object, but I guess that's not a good practice).

So, to answer myself, 'auto joining' in Slick isn't possible, falling back to manual joins with manual object construction.

Custom column (Object) type in Slick

1 Answers