Slick 3.0 & Postgres 9.4: Join the first row

Question

The question SQL Server: How to Join to first row shows the way to achieve in SQL. I'm using slick 3.0, having three tables as:

class ABRelationTable(tag: Tag) extends Table[(Int, Int)](tag, "A_B_RELATION") {
  def aId = column[Int]("A_ID")
  def bId = column[Int]("B_ID")
  def * = (aId, bId)
}

class ACRelationTable(tag: Tag) extends Table[(Int, Int)](tag, "A_C_RELATION") {
  def aId = column[Int]("A_ID")
  def cId = column[Int]("C_ID")
  def * = (aId, cId)
}

class ATable(tag: Tag) extends Table[(Int, String)](tag, "A") {
  def id = column[Int]("ID")
  def data = column[String]("DATA")
  def * = (aId)
}

What I want is to give a bId to get an object A and ONE possible cId. For example:

Table A:

ID           DATA
==================
1            101
2            102
3            103

Table A_B_RELATION

A_ID         B_ID
=================
1            1
2            1
3            2

Table A_C_RELATION

A_ID         C_ID
=================
1            1
2            1
2            2

Given bId = 1, the result can be:

A_ID     DATA    C_ID
=====================
1        101     1
2        102     1

Thanks!

score 0 · Answer 1 · answered Jul 28 '15 at 16:05

The following will be your query .

val query = A.join(A_C_RELATION).on(_.ID === _. A_ID) .join(A_B_RELATION).on(_.ID === _. A_ID).filter(_.B_ID ===)

Where A , A_C_RELATION , A_B_RELATION are tablequery of respective tables .

Refer here for more examples.

Slick 3.0 & Postgres 9.4: Join the first row

1 Answers1