CODEWITHSUNDEEP
scalaslick
thecodejack
thecodejack

Reputation: 13379

How to do joins in slick?

I have 2 queries and want to do a join like sql

For eg i have these tables..

object Family extends Table[Long]("Family") {
  def id = column[Long]("id")
  def * = id
}

object Person extends Table[(Long, Long)]("Person") {
  def id = column[Long]("id")
  def FamilyId = column[Long]("FamilyId")
  def * = id ~ FamilyId ~ size
}

Upvotes: 3

Views: 1107

Answers (3)

Daniel
Daniel

Reputation: 9464

Here's an updated answer for Slick 3.x:

val families = TableQuery[Family]
val people = TableQuery[Person]

val familyJoinQuery = for {
  family <- families
  person <- people if family.id === person.FamilyId
} yield (family, person)

Alternatively, and rarely documented, you can also do the following:

val familyJoinQuery = families.join(people).on((family, person) => family.id === person.FamilyId)

Upvotes: 1

triggerNZ
triggerNZ

Reputation: 4761

From the Slick documentation: http://slick.typesafe.com/doc/1.0.0/lifted-embedding.html#joining-and-zipping, below are some of the possible joins in Slick and some rough SQL associated with it

val implicitCrossJoin = for {
  c <- Coffees
  s <- Suppliers
} yield (c.name, s.name)

//SELECT c.name, s.name FROM Coffees c, Suppliers s


val implicitInnerJoin = for {
  c <- Coffees
  s <- Suppliers if c.supID === s.id
} yield (c.name, s.name)
//SELECT c.name, s.name FROM Coffees c, Suppliers s where c.supID = s.id    

val explicitCrossJoin = for {
  (c, s) <- Coffees innerJoin Suppliers
} yield (c.name, s.name)
//SELECT c.name, s.name FROM Coffees c CROSS JOIN Suppliers s    

val explicitInnerJoin = for {
  (c, s) <- Coffees innerJoin Suppliers on (_.supID === _.id)
} yield (c.name, s.name)
//SELECT c.name, s.name FROM Coffees c INNER JOIN Suppliers s ON (c.supID = s.id)   

val explicitLeftOuterJoin = for {
  (c, s) <- Coffees leftJoin Suppliers on (_.supID === _.id)
} yield (c.name, s.name.?)
//SELECT c.name, s.name FROM Coffees c LEFT OUTER JOIN Suppliers s ON (c.supID = s.id)

val explicitRightOuterJoin = for {
  (c, s) <- Coffees rightJoin Suppliers on (_.supID === _.id)
} yield (c.name.?, s.name)
//SELECT c.name, s.name FROM Coffees c RIGHT OUTER JOIN Suppliers s ON (c.supID = s.id)

val explicitFullOuterJoin = for {
  (c, s) <- Coffees outerJoin Suppliers on (_.supID === _.id)
} yield (c.name.?, s.name.?)
//SELECT c.name, s.name FROM Coffees c FULL OUTER JOIN Suppliers s ON (c.supID = s.id)

So as you can see, most of the constructs map very directly to SQL.

Hope that helps

Upvotes: 1

user506502
user506502

Reputation: 66

val implicitInnerJoin = for {
  c <- Family
  s <- Person if c.id === s.FamilyId
} yield (c.id, s.id)

Upvotes: 5

Related Questions