How to perform leftJoin on tables from different d

Question

I have 2 databases (database1 and database2).

• database1 has table1 with field id
• database2 has table2 with field id

Now how do i perform leftJoin(as shown below) using Slick?

 SELECT tb1.`id`
 FROM `database1`.`table1` t1
 LEFT JOIN `database1`.`table2` t2 ON t1.`id`=t2.`id`

Answer 1

I may be wrong here but most existing relational databases don't allow you to span multiple databases within single operation. However, what you showed above is easily achievable by using schema (and I strongly believe this is what you want to really achieve -judging by the SQL you pasted).

Let's have an example. Let's assume we have two tables defined within our Slick-related code as follows:

  // student
  case class Student(name: String,
                     middleName: Option[String],
                     surname: String,
                     nationality: String,
                     id: Id[Student] = Id.none)

  class StudentTable(tag: Tag) extends Table[Student](tag, "STUDENT") {
    def name = column[String]("NAME")
    def middleName = column[Option[String]]("MIDDLE_NAME")
    def surname = column[String]("SURNAME")
    def nationality = column[String]("NATIONALITY")
    def id = column[Id[Student]]("ID", O.PrimaryKey, O.AutoInc)

    def * = (name, middleName, surname, nationality, id) <> (Student.tupled, Student.unapply)
  }

  lazy val StudentTable = TableQuery[StudentTable]

  // document
  case class Document(studentId: Option[Id[Student]],
                      name: String,
                      uuid: String,
                      id: Id[Document] = Id.none)

  class DocumentTable(tag: Tag) extends Table[Document](tag, "DOCUMENT") {
    def studentId = column[Option[Id[Student]]]("STUDENT_ID")
    def name = column[String]("NAME")
    def uuid = column[String]("UUID")
    def id = column[Id[Document]]("ID", O.PrimaryKey, O.AutoInc)

    def * = (studentId, name, uuid, id) <> (Document.tupled, Document.unapply)

    def student = foreignKey("fk_document_student", studentId, StudentTable)(_.id.?)
  }

  lazy val DocumentTable = TableQuery[DocumentTable]

Doing following query:

    DocumentTable
      .joinLeft(StudentTable).on(_.studentId === _.id)
      .filter { case(doc, student) => student.map(_.name) === "Test" }

will generate following SQL:

select x2."STUDENT_ID", x2."NAME", x2."UUID", x2."ID", x3."NAME", x3."MIDDLE_NAME", x3."SURNAME", x3."NATIONALITY", x3."ID" 
from "DOCUMENT" x2 
left outer join "STUDENT" x3 on x2."STUDENT_ID" = x3."ID" 
where x3."NAME" = 'Test'

If I however change my table definition to:

class StudentTable(tag: Tag) extends Table[Student](tag, _schemaName = Option("database2"), "STUDENT") {
...

and this

class DocumentTable(tag: Tag) extends Table[Document](tag, _schemaName = Option("database1"), "DOCUMENT") {
...

Notice that I added one parameter - _schemaName - which indicates that particular table should be prefixed by specified schema.

I will have (same Slick query) following SQL generated now:

select x2."STUDENT_ID", x2."NAME", x2."UUID", x2."ID", x3."NAME", x3."MIDDLE_NAME", x3."SURNAME", x3."NATIONALITY", x3."ID" 
from "database1"."DOCUMENT" x2 
left outer join "database2"."STUDENT" x3 on x2."STUDENT_ID" = x3."ID" 
where x3."NAME" = 'Test' 

which seems to be precisely what you want to achieve.