-
-
Notifications
You must be signed in to change notification settings - Fork 609
/
ThreeWayJoinTest.scala
130 lines (110 loc) · 3.7 KB
/
ThreeWayJoinTest.scala
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
package com.typesafe.slick.testkit.tests
import com.typesafe.slick.testkit.util.{AsyncTest, RelationalTestDB}
class ThreeWayJoinTest extends AsyncTest[RelationalTestDB] {
import tdb.profile.api.*
// ******************** Full many to many join test (i.e. four table) **********************
def testManyToManyJoin = {
class A(tag: Tag) extends Table[Int](tag, "a_manytomanyjoin") {
def id = column[Int]("id", O.PrimaryKey)
def * = id
def bs = cs.filter(_.aId === id).flatMap(_.b)
}
lazy val as = TableQuery[A]
class B(tag: Tag) extends Table[(Int, Int)](tag, "b_manytomanyjoin") {
def id = column[Int]("id", O.PrimaryKey)
def dId = column[Int]("dId")
def * = (id, dId)
def as = cs.filter(_.bId === id).flatMap(_.a)
def d = foreignKey("d_fk", dId, ds)(_.id)
}
lazy val bs = TableQuery[B]
class C(tag: Tag) extends Table[(Int, Int)](tag, "c_manytomanyjoin") {
def aId = column[Int]("aId")
def bId = column[Int]("bId")
def * = (aId, bId)
def a = foreignKey("a_fk", aId, as)(_.id)
def b = foreignKey("b_fk", bId, bs)(_.id)
}
lazy val cs = TableQuery[C]
class D(tag: Tag) extends Table[Int](tag, "d_manytomanyjoin") {
def id = column[Int]("id", O.PrimaryKey)
def * = id
}
lazy val ds = TableQuery[D]
def q1 = for {
a <- as
b <- a.bs
d <- b.d
} yield (a, b.id, d)
def q2 = for {
a <- as
b <- a.bs.map(b => b)
d <- b.d
} yield (a, b.id, d)
def q3 = for {
a <- as
b <- a.bs.map(b => b).map(b => b)
d <- b.d
} yield (a, b.id, d)
DBIO.seq(
(as.schema ++ bs.schema ++ cs.schema ++ ds.schema).create,
as ++= Seq(1),
ds ++= Seq(3),
bs ++= Seq((2,3)),
cs ++= Seq((1,2)),
q1.result.named("q1").map(_.toSet shouldBe Set((1, 2, 3))),
q2.result.named("q2").map(_.toSet shouldBe Set((1, 2, 3))),
q3.result.named("q3").map(_.toSet shouldBe Set((1, 2, 3)))
)
}
// ******************** Many to many join across two tables **********************
def testManyToManyJoinTwice = {
class A(tag: Tag) extends Table[Int](tag, "a_manytomanyjoin2") {
def id = column[Int]("id", O.PrimaryKey)
def * = id
def bs = atbs.filter(_.aId === id).flatMap(_.b)
}
lazy val as = TableQuery[A]
class ATB(tag: Tag) extends Table[(Int, Int)](tag, "atb_manytomanyjoin2") {
def aId = column[Int]("aId")
def bId = column[Int]("bId")
def * = (aId, bId)
def a = foreignKey("a_fk2", aId, as)(_.id)
def b = foreignKey("b_fk2", bId, bs)(_.id)
}
lazy val atbs = TableQuery[ATB]
class B(tag: Tag) extends Table[Int](tag, "b_manytomanyjoin2") {
def id = column[Int]("id", O.PrimaryKey)
def * = id
def cs = btcs.filter(_.bId === id).flatMap(_.c)
}
lazy val bs = TableQuery[B]
class BTC(tag: Tag) extends Table[(Int, Int)](tag, "btc_manytomanyjoin2") {
def bId = column[Int]("bId")
def cId = column[Int]("cId")
def * = (bId, cId)
def b = foreignKey("b_fk3", bId, bs)(_.id)
def c = foreignKey("c_fk3", cId, cs)(_.id)
}
lazy val btcs = TableQuery[BTC]
class C(tag: Tag) extends Table[Int](tag, "c_manytomanyjoin2") {
def id = column[Int]("id", O.PrimaryKey)
def * = id
}
lazy val cs = TableQuery[C]
def q1 = for {
a <- as
b <- a.bs
c <- b.cs
} yield (a, b.id, c)
DBIO.seq(
(as.schema ++ atbs.schema ++ bs.schema ++ btcs.schema ++ cs.schema).create,
as ++= Seq(1),
bs ++= Seq(2),
cs ++= Seq(3),
atbs ++= Seq((1, 2)),
btcs ++= Seq((2, 3)),
q1.result.named("q1").map(_.toSet shouldBe Set((1, 2, 3)))
)
}
}