CODEWITHSUNDEEP
scalaslick
SkyWalker
SkyWalker

Reputation: 14337

Slick 3.1.x How to implement a Many-to-Many join?

In the UserDao I'm trying to define a method that given a user will get all the security roles assigned to it. This is a many-to-many relationship, here the pseudo database design (here is the actual Tables.scala full generated implementation):

User(PK id)
SecurityRole(PK id)
UserSecurityRole(
  userId FK to User(id), 
  securityRoleId FK to SecurityRole(id)
)

Therefore I try defining my getRoles function like this:

def getRoles(user: UserRow) : Future[List[SecurityRoleRow]] = {
  val action = for {
  role <- SecurityRole join UserSecurityRole on (_.id === _.securityRoleId) 
            join User on (_.userId === _.id)
  } yield role
  db.run(action)
}

or like this:

def getRoles(user: UserRow) : Future[List[SecurityRoleRow]] = {
  val action = for {
    role <- SecurityRole join (UserSecurityRole join User on (_.userId === _.id)) on (_.id === _.securityRoleId)
  } yield role
  db.run(action)
}

but in both cases I get the compiler error that can not resolve symbol _.userId note that the output has to be a Role and thus, I start both alternatives with a SecurityRole because that's what I need to get. My impression is that after it does the first join, some columns are wiped out, or?

In SQL this would be as simple as:

SELECT t1.*
FROM SecurityRole t1, 
     UserSecurityRole t2,
     User t3
WHERE t1.id = t2.securityRoleId
  AND t3.id = t2.userId

that's it. Then if I wanted slickifying this SQL into my UserDao I could do:

def getRoles(user: UserRow) : Future[List[SecurityRoleRow]] = {
  val action = sql"""SELECT t1.* " +
                    "FROM SecurityRole t1, " + 
                         "UserSecurityRole t2, " +
                    "WHERE t1.id = t2.securityRoleId " +
                      "AND ${user.id} = t2.userId""".as[SecurityRoleRow]
    db.run(action)
}

Upvotes: 1

Views: 574

Answers (1)

Paul Dolega
Paul Dolega

Reputation: 2476

You are basically right (well, kind of, they are not wiped out but rather tuple gets extended). Instead of this:

def getRoles(user: UserRow) : Future[List[SecurityRoleRow]] = {
  val action = for {
  role <- SecurityRole join UserSecurityRole on (_.id === _.securityRoleId) 
            join User on (_.userId === _.id)
  } yield role
  db.run(action)
}

(btw you forgot about result call I believe)

you should use:

def getRoles(user: UserRow) : Future[List[SecurityRoleRow]] = {
  val action = (for {
  role <- SecurityRole join UserSecurityRole on (_.id === _.securityRoleId) 
            join User on (_._2.userId === _.id) // here is the change!!
  } yield role).result
  db.run(action)
}

Each new join adds new nested tuple. Look at this slide from my presentation here (I deconstruct this tuple each type with pattern matching but's it's all the same): http://slides.com/pdolega/slick-101#/75 (and especially - take a look at the next slide which makes it all neater by using foreign key; in your case that would be perfect solution).

EDIT:

Even without defining foreign keys (which I think you should) you can change your version to monadic form, which avoids all the tuple nesting:

val action = (for {
  role <- SecurityRole 
  userRole <- UserSecurityRole if role.id === userRole.securityRoleId 
  user <- User if userRole.userId === user.id
} yield role).result

db.run(action)

Upvotes: 3

Related Questions