Outer join with Scala Slick

2
votes

I have two tables, User and UserBusinessPartnerIds

User(id, name, email, whatever)

UserBusinessPartnerIds(userId, bpId)

I am trying to retrieve a user and a list of business partner ids.

The following query works fine! 

database withSession { implicit session: Session =>
      val query = (for {
        (user, userBpid) <- Users leftJoin UserBusinessPartners on (_.id is _.userId)
          if user.email === email &&
             user.password === password &&
             user.active === true &&
             userBpid.dateFrom < today &&
             userBpid.dateTo > today
      } yield (user.id, userBpid.bpId.?))

      val results = query.list

      results.headOption.map( row  => User(row._1, email, password, results.map(_._2).flatten.toSet))
    }

Granted, the end of the code is heinous; but whatever.

The problem is, I wish to do an OUTER join. So if a user doesn't have a corresponding row to a business partner id in the other table, I still want to get that user.

Any ideas?

edit - Edited the question to actually be correct. I still want to return a user even if they dont have business partner ids associated with them

2
scalaslick

2 Answers

1
votes

hmmm, what does the generated sql look like? Should be a left outer join.

I suspect the issue is with:

userBpid.dateFrom < today && userBpid.dateTo > today

As the user + null user-partner row(s) cannot be returned due to applying conditions to potentially null values; i.e. they will never return true when an associated business partner id row does not exist.

As a test, do a println(q.sqlStatement) or look in your query log and run the query manually with and without above query condition.

EDIT Not tested, but try this, aliasing user business table, one inner, one outer. You may get a non-unique table alias compiler error, has happened to me in a previous attempt to work some outer join magic

val q = 
  for{
    (u,upo) <- Users leftJoin UserBusinessPartners on (_.id is _.userId)
      if(u.email is email) && (u.password is password) && (u.active is true)
    up <- UserBusinessPartners
      if(u.id is up.userId) && (up.dateFrom < today) && (up.dateTo > today)
    _ <- Query groupBy(u.id)
  } yield (u.id, upo.bpId.?)
0
votes

You need to filter the UserBusinessPartners table before the join and that will give you users who have no business partners. something like : 

database withSession { implicit session: Session =>
  userBP = UserBusinessPartners.filter(ubp => ubp.dateFrom < today && ubp.dateTo > today)
  val query = (for {
    (user, userbp) <- Users leftJoin userBP on (_.id is _.userId)
      if user.email === email &&
         user.password === password &&
         user.active === true 
  } yield (user.id, userbp.bpId.?))

  val results = query.list

  results.headOption.map( row  => User(row._1, email, password, results.map(_._2).flatten.toSet))
}

The filter will show you a view on table with only desired business partners, the left join will list all users.

Your query failed because of :

userBpid.dateFrom < today && userBpid.dateTo > today

Because a row selected with null userBpid will get filtered out because of above condition.