Reputation: 14337
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
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