ScalikeJDBC Left Join Query Example

build.sbt

libraryDependencies ++= Seq(
  "com.github.seratch" %% "scalikejdbc" % "[1.3,)",
  "org.slf4j" % "slf4j-simple" % "1.6.4",
  "org.hsqldb" % "hsqldb" % "[2,)"
)

left_join_sample.scala

import scalikejdbc._

// settings
Class.forName("org.hsqldb.jdbc.JDBCDriver")
ConnectionPool.singleton("jdbc:hsqldb:mem:test", "", "")
GlobalSettings.loggingSQLAndTime = LoggingSQLAndTimeSettings(enabled = true, logLevel = 'info)

// prepare database
DB autoCommit { implicit s =>
  try {
    SQL("create table users(id bigint primary key not null, name varchar(255), group_id bigint)").execute.apply()
    SQL("insert into users values ({id}, {name}, {groupId})").bindByName('id -> 1, 'name -> "Andy", 'groupId -> 1).update.apply()
    SQL("insert into users values ({id}, {name}, {groupId})").bindByName('id -> 2, 'name -> "Brian", 'groupId -> 999).update.apply()
    SQL("create table groups(id bigint primary key not null, name varchar(255))").execute.apply()
    SQL("insert into groups values ({id}, {name})").bindByName('id -> 1, 'name -> "Team A").update.apply()
  } catch { case e => println(e.getMessage) }
}

// generated by mapper-generator
class Group(val id: Long, val name: String)
class User(val id: Long, name: String, val groupId: Long)
object User {
  val * = (rs: WrappedResultSet) => new User(rs.long("id"), rs.string("name"), rs.long("group_id"))
}

// created for join query
class UserWithGroup(val user: User, val group: Option[Group])
object UserWithGroup {

  val * = (rs: WrappedResultSet) => new UserWithGroup(
    user = User.*(rs), 
    group = rs.longOpt("g_id").map(id => new Group(id, rs.string("g_name")))
  )

  def all()(implicit  s: DBSession) : List[UserWithGroup] = {
    SQL("select users.*, groups.id as g_id, groups.name as g_name from users left join groups on users.group_id = groups.id").map(*).list.apply()
  }

}

implicit val session = DB.autoCommitSession

UserWithGroup.all.foreach ((e) => println(e.user.id + "," + e.group))

/*
1,Some($line6.$read$$iw$$iw$$iw$$iw$Group@150c2c)
2,None
 */