Join on two foreign keys from same table in scalikejdbc

Question

So i have a one table that has two FK that points at same table.

For example:

Message table with columns sender and receiver that both references id in user table.

When i'm writing query to fetch message and join on both the result is same use for both, the first one.

Here is how i'm trying to do it.

import scalikejdbc._

Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

implicit val session = AutoSession

sql"""
create table members (
  id serial not null primary key,
  name varchar(64),
  created_at timestamp not null
)
""".execute.apply()

sql"""
create table message (
  id serial not null primary key,
  msg varchar(64) not null,
  sender int not null,
  receiver int not null
)
""".execute.apply()

Seq("Alice", "Bob", "Chris") foreach { name =>
  sql"insert into members (name, created_at) values (${name}, current_timestamp)".update.apply()
}

Seq(
    ("msg1", 1, 2),
    ("msg2", 1, 3),
    ("msg3", 2, 1)
) foreach { case (m, s, r) =>
  sql"insert into message (msg, sender, receiver) values (${m}, ${s}, ${r})".update.apply()
}

import org.joda.time._
case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(mem: ResultName[Member])(rs: WrappedResultSet): Member = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.jodaDateTime("created_at"))
}

case class Message(id: Long, msg: String, sender: Member, receiver: Member)
object Message extends SQLSyntaxSupport[Message] {
    override val tableName = "message"
  def apply(ms: ResultName[Message], s: ResultName[Member], r: ResultName[Member])(rs: WrappedResultSet): Message = new Message(
    rs.long("id"), rs.string("msg"), Member(s)(rs), Member(r)(rs))
}

val mem = Member.syntax("m")
val s = Member.syntax("s")
val r = Member.syntax("r")
val ms = Message.syntax("ms")
val msgs: List[Message] = sql"""
  select * 
  from ${Message.as(ms)}
  join ${Member.as(s)} on ${ms.sender} = ${s.id}
  join ${Member.as(r)} on ${ms.receiver} = ${r.id}
  """.map(rs => Message(ms.resultName, s.resultName, r.resultName)(rs)).list.apply()

Am I doing something wrong or is it bug?

score 0 · Answer 1 · answered Mar 26 '15 at 09:45

Sorry for late reply. We have the Google Group ML and I actively read notifications from the group.

When you're in a hurry, please post stackoverflow URLs there. https://groups.google.com/forum/#!forum/scalikejdbc-users-group

In this case, you need to write select ${ms.result.*}, ${s.result.*} instead of select *. Please read this page for details. http://scalikejdbc.org/documentation/sql-interpolation.html

Join on two foreign keys from same table in scalikejdbc

1 Answers1