Skip to main content

Joins

Start by importing:

import scalaql._

// Docs classes
import scalaql.docs.Hogwarts._

Inner join​

Scala QL allows you to join multiple queries based on condition.

This could be done as follows:

val joinQuery = select[Student]
  .join(select[Faculty])
  .on(_.faculty == _.name)
// joinQuery: Query[From[Student] with From[Faculty], (Student, Faculty)] = FROM(Hogwarts::Student) INNER JOIN FROM(Hogwarts::Faculty)

For convenience, we'll wrap the join result into something more meaningful:

case class StudentAndFaculty(student: Student, faculty: Faculty)

val studentsAndFaculties = joinQuery.map((StudentAndFaculty.apply _).tupled)
// studentsAndFaculties: Query[From[Student] with From[Faculty], StudentAndFaculty] = FROM(Hogwarts::Student) INNER JOIN FROM(Hogwarts::Faculty) -> MAP(MdocSession::App::StudentAndFaculty)

It will produce the following result:

studentsAndFaculties
  .show(truncate = false)
  .run(
    from(students) & from(faculties)
  )
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |student.name|student.age|student.faculty|student.grade|student.specialization|student.birthDay|faculty.name|faculty.founder  |faculty.description  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |Harry       |19         |Gryffindor     |85.1         |getting into troubles |1980-07-31      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Ron         |18         |Gryffindor     |66.2         |eating                |1980-05-01      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Hermione    |18         |Gryffindor     |99.6         |learning              |1979-09-17      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Draco       |18         |Slytherin      |85.1         |trolling              |1980-06-05      |Slytherin   |Salazar Slytherin|The bad guys         |
// |Cedric      |17         |Hufflepuff     |90.1         |young dying           |1977-10-01      |Hufflepuff  |Helga Hufflepuff |Cedric studied here  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
//

Note on join semantics​

Scala QL follows the SQL standard in case of joins.
It means that if the Query input has duplicates, join will produce a result with duplicates.

For instance, with the following input:

val duplicatedStudents = students ++ students
// duplicatedStudents: List[Student] = List(
//   Student(
//     name = "Harry",
//     age = 19,
//     faculty = "Gryffindor",
//     grade = 85.1,
//     specialization = "getting into troubles",
//     birthDay = 1980-07-31
//   ),
//   Student(
//     name = "Ron",
//     age = 18,
//     faculty = "Gryffindor",
//     grade = 66.2,
//     specialization = "eating",
//     birthDay = 1980-05-01
//   ),
//   Student(
//     name = "Hermione",
//     age = 18,
//     faculty = "Gryffindor",
//     grade = 99.6,
//     specialization = "learning",
//     birthDay = 1979-09-17
//   ),
//   Student(
//     name = "Draco",
//     age = 18,
//     faculty = "Slytherin",
//     grade = 85.1,
//     specialization = "trolling",
//     birthDay = 1980-06-05
//   ),
//   Student(
//     name = "Cedric",
//     age = 17,
//     faculty = "Hufflepuff",
//     grade = 90.1,
//     specialization = "young dying",
//     birthDay = 1977-10-01
//   ),
//   Student(
//     name = "Harry",
//     age = 19,
//     faculty = "Gryffindor",
//     grade = 85.1,
//     specialization = "getting into troubles",
//     birthDay = 1980-07-31
//   ),
// ...

You'll get a result with duplicates:

studentsAndFaculties
  .show(truncate = false)
  .run(
    from(duplicatedStudents) & from(faculties)
  )
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |student.name|student.age|student.faculty|student.grade|student.specialization|student.birthDay|faculty.name|faculty.founder  |faculty.description  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |Harry       |19         |Gryffindor     |85.1         |getting into troubles |1980-07-31      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Ron         |18         |Gryffindor     |66.2         |eating                |1980-05-01      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Hermione    |18         |Gryffindor     |99.6         |learning              |1979-09-17      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Draco       |18         |Slytherin      |85.1         |trolling              |1980-06-05      |Slytherin   |Salazar Slytherin|The bad guys         |
// |Cedric      |17         |Hufflepuff     |90.1         |young dying           |1977-10-01      |Hufflepuff  |Helga Hufflepuff |Cedric studied here  |
// |Harry       |19         |Gryffindor     |85.1         |getting into troubles |1980-07-31      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Ron         |18         |Gryffindor     |66.2         |eating                |1980-05-01      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Hermione    |18         |Gryffindor     |99.6         |learning              |1979-09-17      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Draco       |18         |Slytherin      |85.1         |trolling              |1980-06-05      |Slytherin   |Salazar Slytherin|The bad guys         |
// |Cedric      |17         |Hufflepuff     |90.1         |young dying           |1977-10-01      |Hufflepuff  |Helga Hufflepuff |Cedric studied here  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
//

To avoid duplicates, you may use deduplicate method:

val deduplicatedQuery = studentsAndFaculties.deduplicate
// deduplicatedQuery: Query[From[Student] with From[Faculty], StudentAndFaculty] = FROM(Hogwarts::Student) INNER JOIN FROM(Hogwarts::Faculty) -> MAP(MdocSession::App::StudentAndFaculty) -> DEDUPLICATE BY(MdocSession::App::StudentAndFaculty)

This will produce a result without duplicates:

deduplicatedQuery
  .show(truncate = false)
  .run(
    from(duplicatedStudents) & from(faculties)
  )
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |student.name|student.age|student.faculty|student.grade|student.specialization|student.birthDay|faculty.name|faculty.founder  |faculty.description  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |Harry       |19         |Gryffindor     |85.1         |getting into troubles |1980-07-31      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Ron         |18         |Gryffindor     |66.2         |eating                |1980-05-01      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Hermione    |18         |Gryffindor     |99.6         |learning              |1979-09-17      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Draco       |18         |Slytherin      |85.1         |trolling              |1980-06-05      |Slytherin   |Salazar Slytherin|The bad guys         |
// |Cedric      |17         |Hufflepuff     |90.1         |young dying           |1977-10-01      |Hufflepuff  |Helga Hufflepuff |Cedric studied here  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
//

If you need to deduplicate by a specific field, you may use deduplicateBy method:

val deduplicatedByQuery = studentsAndFaculties.deduplicateBy(_.student.name)
// deduplicatedByQuery: Query[From[Student] with From[Faculty], StudentAndFaculty] = FROM(Hogwarts::Student) INNER JOIN FROM(Hogwarts::Faculty) -> MAP(MdocSession::App::StudentAndFaculty) -> DEDUPLICATE BY(String)

This will produce a result without duplicates:

deduplicatedByQuery
  .show(truncate = false)
  .run(
    from(duplicatedStudents) & from(faculties)
  )
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |student.name|student.age|student.faculty|student.grade|student.specialization|student.birthDay|faculty.name|faculty.founder  |faculty.description  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
// |Harry       |19         |Gryffindor     |85.1         |getting into troubles |1980-07-31      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Ron         |18         |Gryffindor     |66.2         |eating                |1980-05-01      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Hermione    |18         |Gryffindor     |99.6         |learning              |1979-09-17      |Gryffindor  |Godric Gryffindor|100 points by default|
// |Draco       |18         |Slytherin      |85.1         |trolling              |1980-06-05      |Slytherin   |Salazar Slytherin|The bad guys         |
// |Cedric      |17         |Hufflepuff     |90.1         |young dying           |1977-10-01      |Hufflepuff  |Helga Hufflepuff |Cedric studied here  |
// +------------+-----------+---------------+-------------+----------------------+----------------+------------+-----------------+---------------------+
//