Foreign key relationship in Slick 2.1.0 and Traits

Question

So I have an existing project. I didn't write any of this, and the author's choice of how they implemented Slick confuses me somewhat.

Here is an existing table/slick set of classes:

case class SourcesRow(id: Long,
                      childSourceId: Long,
                      childSourceName: String,
                      parentSourceId: Long,
                      parentSourceName: String)

trait SourcesTable { this : DbProfile =>

  import profile.simple._

  class SourcesRows(tag : Tag) extends Table[SourcesRow](tag, "Sources") {
    def id = column[Long]("Id", O.PrimaryKey, O.NotNull, O.AutoInc)
    def childSourceId = column[Long]("ChildSourceId", O.NotNull)
    def childSourceName = column[String]("ChildSourceName", O.NotNull)
    def parentSourceId = column[Long]("ParentSourceId", O.NotNull)
    def parentSourceName = column[String]("ParentSourceName", O.NotNull)

    def * = (id, childSourceId, childSourceName, parentSourceId, parentSourceName) <> (SourcesRow.tupled, SourcesRow.unapply)
  }

  val sources = TableQuery[SourcesRows]

  object SourcesTable {
    def listSources()(implicit session: SessionDef) =
      sources.run
  }

}

...and we have several of them which are loaded into a database object like so

class ControlledValuesDb(override val profile: JdbcProfile) extends DbProfile
  with RestrictionsTable
  with RestrictionCategoriesTable
  with SourcesTable
  with CollectionsTable
  with SiteDestinationsTable
  with SupplementalCategoriesTable
  with ListsTable
  with ItemsTable {
...
}

Now I'm trying to add a table with a relationship (none of those tables have any relationships. I've been looking at the Slick 2.1 docs and it looks like I need to reference one TableQuery from the object, but I'm not quite sure how to accomplish that. See the ??? below:

case class ItemsRow(id: Long , listId: Long, value: String)
case class ListsRow(id: Long, name: String)

trait ListsTable { this: DbProfile =>

  import profile.simple._

  class ListsRows(tag: Tag) extends Table[ListsRow](tag, "Lists") {
    def id = column[Long]("Id", O.PrimaryKey, O.NotNull, O.AutoInc)
    def name = column[String]("Name", O.NotNull)

    def * = (id, name) <> (ListsRow.tupled, ListsRow.unapply)
  }

  val lists = TableQuery[ListsRows]

  object ListsTable {

  }

}

trait ItemsTable { this: DbProfile =>

  import profile.simple._

  class ItemsRows(tag : Tag) extends Table[ItemsRow](tag, "Items") {
    def id = column[Long]("Id", O.PrimaryKey, O.NotNull, O.AutoInc)
    def listId = column[Long]("ListId", O.NotNull)
    def value = column[String]("Val", O.NotNull)
    //def list = foreignKey("fk_item_list_id", listId, ???)(_.id)

    def * = (id, listId, value) <> (ItemsRow.tupled, ItemsRow.unapply)
  }

  val items = TableQuery[ItemsRows]

  object ItemsTable {

  }

}

Answer 1

If what you want is to have a table with relationship

You can model as follow

class PostTable(tag: Tag) extends Table[BlogPost](tag, "posts") {
  def pid = column[Long]("pid", O.PrimaryKey, O.AutoInc)
  def author = column[String]("author") // fk of user table

  def userFK =
    foreignKey("author_fk", author, TableQuery[UserTable])(_.email, ForeignKeyAction.Restrict, ForeignKeyAction.Cascade)

  def * = (pid, author, title, content, postAt, tags) <> (BlogPost.tupled, BlogPost.unapply)
}

class UserTable(tag: Tag) extends Table[User](tag, "users") {
  def email = column[String]("email", O.PrimaryKey)
  def * = (email, password, name) <> (User.tupled, User.unapply)
}

Notice userFK in PostTable is a fk constraint TableQuery is just an object that you can use to query your database

For example you have val sources = TableQuery[SourcesRows] in your code, which you can then do

sources.filter(_.pid === 1001L) 

which means select * from Sources where pid = 1001;

Hope this help =)