slick schema leftJoin produce Cartesian product

Question

I have this two tables :

    select * from "DEPARTMENTS";
 ID | NAME  | MANAGER_ID 
----+-------+------------
  1 | FOO   |          1
  3 | XXX   |          2
  4 | dept1 |           
(3 rows)

select * from "EMPLOYEES";
 NAME | LAST |      EMAIL       | PHONE | SKYPE | DEPT_ID | ID 
------+------+------------------+-------+-------+---------+----
 AAA  | xxxx | [email protected] |       |       |         |  1
 BBB  | yyyy |                  |       |       |         |  2
(2 rows)

I want to get all departments with manager's name (if manager exists) total of 3 rows .

however in my schema when I do:

import scala.slick.driver.PostgresDriver.simple._
import play.api.Play.current
case class DepartmentManager(id:Int,name:String,manager:String="")
case class Department(id:Option[Int],name:String,managerId:Option[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)
}

object Departments{
  val db = play.api.db.slick.DB
  val departments = TableQuery[Departments]
//this is the problematic query
  def allWithMngr = db.withSession { implicit session =>
    val employees = TableQuery[Employees]
    val dm = (departments leftJoin employees ).list
    dm.map(x =>DepartmentManager(x._1.id.getOrElse(0),x._1.name, x._2.name + " " + x._2.last))
  }
}

I am getting Cartesian product (dm.size = 6 )

trying to query like this:

val dm = (departments leftJoin employees on((a,b)=> a.id === b.id)).list

result an error:

[SlickException: Read NULL value (null) for ResultSet column Path s2._4]

This is my employees model:

case class Employee(name: String,last: String,email:Option[String]=None,phone:Option[String]=None,skype:Option[String]=None,department: Option[Int] = None, id: Option[Int] = None)
class Employees (tag: Tag) extends Table[Employee](tag, "EMPLOYEES") {
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME", O.NotNull)
  def last = column[String]("LAST", O.NotNull)
  def email = column[String]("EMAIL", O.Nullable)
  def phone = column[String]("PHONE", O.Nullable)
  def skype = column[String]("SKYPE", O.Nullable)
  def deptId = column[Int]("DEPT_ID", O.Nullable)
  def dept = foreignKey("EMP_FK",deptId,departments)(_.id)
  def * = (name,last,email.?,phone.?,skype.?,deptId.?, id.?) <> (Employee.tupled, Employee.unapply)
val departments = TableQuery[Departments]

}

as Output I want the equivalent to this native Sql :

val query =sql""" select x."ID", x."NAME", y."NAME" from "DEPARTMENTS" x  LEFT OUTER JOIN "EMPLOYEES" y ON (x."MANAGER_ID" = y."ID");""".as[DepartmentManager].list

which will result :

List (DepartmentManager(1,"FOO","AAA"),DepartmentManager(3,"XXX","BBB"),DepartmentManager(4,"FOO",""))

Answer 1

What you're running into here is the awkward way Slick 2 deals with nulls in joins. Things are better in Slick 3, but we can get the join to work in Slick 2.

The query you have...

(departments leftJoin employees ).list

...will be a cross join as you don't have a join.

The error you run into when you use the join is because one of the columns you want to select is NULL. With Slick 2 you have to explicitly pick out these columns and promote them with .? to an option:

val query = 
   departments.leftJoin(employees).on(_.managerId === _.id )
              .map { case (d, e) => (d.id, d.name, e.name.?) }

Note the e.name.? --- because there might not be an e (employee) for the department.

The result of this query with your test data is:

(1, FOO,   Some(AAA))
(2, XXX,   Some(BBB))
(3, dept1, None)

...which I think is what you're after.