UnexpectedNullableFound on left (self) join on aliased table

Question

I am having a simple employee table, which has manager_id by which it can refer it self. I am trying to get an employee along with his manager

Here are my Parsers

object User{

  val simple = {
   get[Option[Int]]("users.user_id")~
   str("users.email")~
   get[Option[String]]("users.first_name")~
   get[Option[String]]("users.last_name")~
   get[Option[String]]("users.nick_name")~
   bool("users.is_manager")~
   bool("users.is_hr_manager")~
   bool("users.is_admin")~
   get[Option[Int]]("users.manager_id") map {
    case user_id~email~first_name~last_name~nick_name~is_manager~is_hr_manager~is_admin~manager_id  => {
        User(user_id,email,first_name,last_name,nick_name,is_manager,is_hr_manager,is_admin,manager_id)
      }
    }
  }

  def getAliasedParser(alias:String):RowParser[User] = {
     getAliased[Option[Int]](alias+"_user_id")~
     getAliased[String](alias+"_email")~
     getAliased[Option[String]](alias+"_first_name")~
     getAliased[Option[String]](alias+"_last_name")~
     getAliased[Option[String]](alias+"_nick_name")~
     getAliased[Boolean](alias+"_is_manager")~
     getAliased[Boolean](alias+"_is_hr_manager")~
     getAliased[Boolean](alias+"_is_admin")~
     getAliased[Option[Int]](alias+"_manager_id") map {
      case user_id~email~first_name~last_name~nick_name~is_manager~is_hr_manager~is_admin~manager_id  => {
          User(user_id,email,first_name,last_name,nick_name,is_manager,is_hr_manager,is_admin,manager_id)
        }
      }
  }

 val withManager = User.simple ~ (User.getAliasedParser("mgr") ?) map {
    case user~manager => (user,manager)
  }

UnexpectedNullableFound(ColumnName(users.email,Some(mgr_email)))]

Here is my query:

def findByEmailWithManager(email: String): Option[(User,Option[User])] = {
    DB.withConnection { implicit connection =>
      SQL("""
          SELECT 
          `users`.`user_id`,
          `users`.`email`,    
          `users`.`first_name`,     
          `users`.`last_name`,    
          `users`.`nick_name`,    
          `users`.`is_manager`,     
          `users`.`is_hr_manager`,    
          `users`.`is_admin`,    
          `users`.`manager_id` ,
          b.`user_id` mgr_user_id,
          b.`email` mgr_email,    
          b.`first_name` mgr_first_name,     
          b.`last_name` mgr_last_name,    
          b.`nick_name` mgr_nick_name,    
          b.`is_manager` mgr_is_manager,     
          b.`is_hr_manager` mgr_is_hr_manager,    
          b.`is_admin` mgr_is_admin,    
          b.`manager_id` mgr_manager_id
          FROM `users` left outer join `users` b on (users.manager_id = b.user_id)
          where `users`.email = {email}
          """).on('email -> email).as(User.withManager.singleOpt)
    }
  }

I get this error when I try execute the query

UnexpectedNullableFound(ColumnName(users.email,Some(mgr_email)))]

Here is my case class

case class User(id:Option[Int]=None,email:String,firstName:Option[String]=None,lastName:Option[String]=None,nickName:Option[String]=None,isManager:Boolean=false,isHrManager:Boolean=false,isAdministrator:Boolean=false,managerId:Option[Int]=None)

Could someone help me understand why i get this error? Thanks in advance

Answer 1

All column that can be null need to be parsed as option: https://github.com/playframework/anorm/blob/master/docs/manual/working/scalaGuide/main/sql/ScalaAnorm.md#working-with-optionalnullable-columns