slick schema - fetch joined tables

Question

doing my first steps with slick, I have this Tables

case class Employee(name: String,last: String,department: Option[Int] = None,id: Option[Int] = None)
class Employees (tag: Tag) extends Table[Employee](tag, "EMPLOYEES") {
  // Auto Increment the id primary key column
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME", O.NotNull)
  def last = column[String]("LAST", O.NotNull)
  def dept = foreignKey("EMP_FK",deptId,departments)(_.id)
  def * = (name,last,deptId.?, id.?) <> (Employee.tupled, Employee.unapply)
  val departments = TableQuery[Departments]
}
case class DepartmentManager(id:Int,name:String,manager:String)
case class Department(id:Option[Int],name:String,managerId:Int)
class Departments (tag: Tag) extends Table[Department](tag, "DEPARTMENTS") {
  val employees = TableQuery[Employees]
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME", O.NotNull)
  def managerId = column[Int]("MANAGER_ID", O.Nullable)
  def manager = foreignKey("EMP_FK",managerId,employees)(_.id)
  def * = (id.?,name,managerId) <> (Department.tupled, Department.unapply)
}

but I'm getting compilation error :

Expression of type Query[Nothing,Nothing,Seq] does not conform to expected type List[DepartmentManager

I tried do something like this (just to check , I know it's bad) :

def all: List[DepartmentManager] =  db.withSession { implicit session =>
    val employees = TableQuery[Employees]
    val x = for {
      (d, e) <- departments join employees
    } yield (d.id, d.name, e.name + " " + e.last)

    x.iterator.map(t=> DepartmentManager(t._1,t._2,t._3)).toList
  }

but it didn't gave me the desired results - the result of the latter (t._1,t._2,t._3) looks like

(1,Foo, (EMPLOYEES Path @1076478352._2).NAME (EMPLOYEES Path @1076478352._2).LAST)

please advice

Answer 1

case class Employee(name: String, last: String, department: Option[Int] = None, id: Option[Int] = None)

class Employees (tag: Tag) extends Table[Employee](tag, "EMPLOYEES") {
    // Auto Increment the id primary key column
    val departments = TableQuery[Departments]

    def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
    def name = column[String]("NAME", O.NotNull)
    def last = column[String]("LAST", O.NotNull)
    def deptId = column[Int]("DEPARTMENT", O.NotNull)

    def deptFK = foreignKey("EMP_DEP_FK", deptId, departments)(_.id)

    def * = (name, last, deptId.?, id.?) <>(Employee.tupled, Employee.unapply)
}

case class DepartmentManager(id: Int, name: String, manager: String)

case class Department(id: Option[Int], name: String, managerId: Int)

class Departments (tag: Tag) extends Table[Department](tag, "DEPARTMENTS") {
    val employees = TableQuery[Employees]
    def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
    def name = column[String]("NAME", O.NotNull)
    def managerId = column[Int]("MANAGER_ID", O.Nullable)

    def managerFK = foreignKey("DEP_MAN_FK", managerId, employees)(_.id)

    def * = (id.?, name, managerId) <>(Department.tupled, Department.unapply)
}


def all(implicit s: Session): List[DepartmentManager] = {
    val employees = TableQuery[Employees]
    val x = for {
        (d, e) <- departments join employees on (_.managerId === _.id)
    } yield (d.id, d.name, e.name + " " + e.last)

    x.list.map(t => DepartmentManager(t._1, t._2, t._3))
}

You need implicit Session object to call list or iterator method on a query.