1096 lines
44 KiB
Swift
1096 lines
44 KiB
Swift
import XCTest
|
|
import GRDB
|
|
|
|
/// Test SQL generation
|
|
|
|
class AssociationHasManyThroughSQLTests: GRDBTestCase {
|
|
|
|
func testBelongsToHasMany() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let b = belongsTo(B.self)
|
|
static let c = hasMany(C.self, through: b, using: B.c)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["bId"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let c = hasMany(C.self)
|
|
}
|
|
struct C: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
|
|
try testHasManyWithTwoSteps(
|
|
db, ab: A.b, ac: A.c,
|
|
bCondition: "\"b\".\"id\" = \"a\".\"bId\"",
|
|
cCondition: "\"c\".\"bId\" = \"b\".\"id\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.c), """
|
|
SELECT "c".* FROM "c" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."id" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testHasOneHasMany() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let b = hasOne(B.self)
|
|
static let c = hasMany(C.self, through: b, using: B.c)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let c = hasMany(C.self)
|
|
}
|
|
struct C: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("a")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
|
|
try testHasManyWithTwoSteps(
|
|
db, ab: A.b, ac: A.c,
|
|
bCondition: "\"b\".\"aId\" = \"a\".\"id\"",
|
|
cCondition: "\"c\".\"bId\" = \"b\".\"id\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.c), """
|
|
SELECT "c".* FROM "c" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testHasManyBelongsTo() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let b = hasMany(B.self)
|
|
static let c = hasMany(C.self, through: b, using: B.c)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let c = belongsTo(C.self)
|
|
}
|
|
struct C: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("c")
|
|
t.belongsTo("a")
|
|
}
|
|
|
|
try testHasManyWithTwoSteps(
|
|
db, ab: A.b, ac: A.c,
|
|
bCondition: "\"b\".\"aId\" = \"a\".\"id\"",
|
|
cCondition: "\"c\".\"id\" = \"b\".\"cId\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.c), """
|
|
SELECT "c".* FROM "c" \
|
|
JOIN "b" ON ("b"."cId" = "c"."id") AND ("b"."aId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testHasManyHasOne() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let b = hasMany(B.self)
|
|
static let c = hasMany(C.self, through: b, using: B.c)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let c = hasOne(C.self)
|
|
}
|
|
struct C: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("a")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
|
|
try testHasManyWithTwoSteps(
|
|
db, ab: A.b, ac: A.c,
|
|
bCondition: "\"b\".\"aId\" = \"a\".\"id\"",
|
|
cCondition: "\"c\".\"bId\" = \"b\".\"id\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.c), """
|
|
SELECT "c".* FROM "c" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
private func testHasManyWithTwoSteps<BAssociation, CAssociation>(
|
|
_ db: Database,
|
|
ab: BAssociation,
|
|
ac: CAssociation,
|
|
bCondition: String,
|
|
cCondition: String) throws
|
|
where
|
|
BAssociation: Association,
|
|
CAssociation: Association,
|
|
BAssociation.OriginRowDecoder: TableRecord,
|
|
BAssociation.OriginRowDecoder == CAssociation.OriginRowDecoder
|
|
{
|
|
let A = BAssociation.OriginRowDecoder.self
|
|
|
|
do {
|
|
try assertEqualSQL(db, A.including(optional: ac), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(required: ac), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(optional: ac), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(required: ac), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
}
|
|
do {
|
|
try assertEqualSQL(db, A.including(optional: ac).including(optional: ab), """
|
|
SELECT "a".*, "b".*, "c".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(optional: ac).including(required: ab), """
|
|
SELECT "a".*, "b".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(optional: ac).joining(optional: ab), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(optional: ac).joining(required: ab), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
}
|
|
do {
|
|
try assertEqualSQL(db, A.including(required: ac).including(optional: ab), """
|
|
SELECT "a".*, "b".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(required: ac).including(required: ab), """
|
|
SELECT "a".*, "b".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(required: ac).joining(optional: ab), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.including(required: ac).joining(required: ab), """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
}
|
|
do {
|
|
try assertEqualSQL(db, A.joining(optional: ac).including(optional: ab), """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(optional: ac).including(required: ab), """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(optional: ac).joining(optional: ab), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(optional: ac).joining(required: ab), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
LEFT JOIN "c" ON \(cCondition)
|
|
""")
|
|
}
|
|
do {
|
|
try assertEqualSQL(db, A.joining(required: ac).including(optional: ab), """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(required: ac).including(required: ab), """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(required: ac).joining(optional: ab), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
try assertEqualSQL(db, A.joining(required: ac).joining(required: ab), """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bCondition) \
|
|
JOIN "c" ON \(cCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testAssociationFilteredByOtherAssociation() throws {
|
|
struct Pet: TableRecord {
|
|
static let child = belongsTo(Child.self)
|
|
}
|
|
struct Toy: TableRecord { }
|
|
struct Child: TableRecord {
|
|
static let toy = hasOne(Toy.self)
|
|
static let pets = hasMany(Pet.self)
|
|
}
|
|
struct Parent: TableRecord, EncodableRecord {
|
|
static let children = hasMany(Child.self)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "parent") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "child") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("parent")
|
|
}
|
|
try db.create(table: "toy") { t in
|
|
t.belongsTo("child")
|
|
}
|
|
try db.create(table: "pet") { t in
|
|
t.belongsTo("child")
|
|
}
|
|
}
|
|
|
|
try dbQueue.inDatabase { db in
|
|
do {
|
|
let association = Parent.hasMany(
|
|
Pet.self,
|
|
through: Parent.children.joining(required: Child.toy),
|
|
using: Child.pets)
|
|
try assertEqualSQL(db, Parent.all().including(required: association), """
|
|
SELECT "parent".*, "pet".* \
|
|
FROM "parent" \
|
|
JOIN "child" ON "child"."parentId" = "parent"."id" \
|
|
JOIN "toy" ON "toy"."childId" = "child"."id" \
|
|
JOIN "pet" ON "pet"."childId" = "child"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent.all().joining(required: association), """
|
|
SELECT "parent".* \
|
|
FROM "parent" \
|
|
JOIN "child" ON "child"."parentId" = "parent"."id" \
|
|
JOIN "toy" ON "toy"."childId" = "child"."id" \
|
|
JOIN "pet" ON "pet"."childId" = "child"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent().request(for: association), """
|
|
SELECT "pet".* \
|
|
FROM "pet" \
|
|
JOIN "child" ON ("child"."id" = "pet"."childId") AND ("child"."parentId" = 1) \
|
|
JOIN "toy" ON "toy"."childId" = "child"."id"
|
|
""")
|
|
}
|
|
do {
|
|
let association = Parent.hasMany(
|
|
Pet.self,
|
|
through: Parent.children.filter(sql: "1 + 1"),
|
|
using: Child.pets.joining(required: Pet.child.filter(sql: "1").joining(required: Child.toy)))
|
|
try assertEqualSQL(db, Parent.all().including(required: association), """
|
|
SELECT "parent".*, "pet".* \
|
|
FROM "parent" \
|
|
JOIN "child" "child1" ON ("child1"."parentId" = "parent"."id") AND (1 + 1) \
|
|
JOIN "pet" ON "pet"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child2"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent.all().joining(required: association), """
|
|
SELECT "parent".* \
|
|
FROM "parent" \
|
|
JOIN "child" "child1" ON ("child1"."parentId" = "parent"."id") AND (1 + 1) \
|
|
JOIN "pet" ON "pet"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child2"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent().request(for: association), """
|
|
SELECT "pet".* \
|
|
FROM "pet" \
|
|
JOIN "child" "child1" ON ("child1"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1 + 1) AND ("child2"."parentId" = 1)
|
|
""")
|
|
}
|
|
do {
|
|
let association = Parent.hasMany(
|
|
Pet.self,
|
|
through: Parent.children.filter(sql: "1 + 1"),
|
|
using: Child.pets)
|
|
.joining(required: Pet.child.filter(sql: "1").joining(required: Child.toy))
|
|
try assertEqualSQL(db, Parent.all().including(required: association), """
|
|
SELECT "parent".*, "pet".* \
|
|
FROM "parent" \
|
|
JOIN "child" "child1" ON ("child1"."parentId" = "parent"."id") AND (1 + 1) \
|
|
JOIN "pet" ON "pet"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child2"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent.all().joining(required: association), """
|
|
SELECT "parent".* \
|
|
FROM "parent" \
|
|
JOIN "child" "child1" ON ("child1"."parentId" = "parent"."id") AND (1 + 1) \
|
|
JOIN "pet" ON "pet"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child2"."id"
|
|
""")
|
|
try assertEqualSQL(db, Parent().request(for: association), """
|
|
SELECT "pet".* \
|
|
FROM "pet" \
|
|
JOIN "child" "child1" ON ("child1"."id" = "pet"."childId") AND (1) \
|
|
JOIN "toy" ON "toy"."childId" = "child1"."id" \
|
|
JOIN "child" "child2" ON ("child2"."id" = "pet"."childId") AND (1 + 1) AND ("child2"."parentId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Three Steps
|
|
|
|
func testHasManyHasManyHasMany() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let bs = hasMany(B.self)
|
|
static let cs = hasMany(C.self, through: bs, using: B.cs)
|
|
static let dsThroughCs = hasMany(D.self, through: cs, using: C.ds)
|
|
static let dsThroughBs = hasMany(D.self, through: bs, using: B.ds)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let cs = hasMany(C.self)
|
|
static let ds = hasMany(D.self, through: cs, using: C.ds)
|
|
}
|
|
struct C: TableRecord {
|
|
static let ds = hasMany(D.self)
|
|
}
|
|
struct D: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("a")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
try db.create(table: "d") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("c")
|
|
}
|
|
|
|
try testHasManyWithThreeSteps(
|
|
db, bs: A.bs, cs: A.cs, dsThroughCs: A.dsThroughCs, dsThroughBs: A.dsThroughBs,
|
|
bsCondition: "\"b\".\"aId\" = \"a\".\"id\"",
|
|
csCondition: "\"c\".\"bId\" = \"b\".\"id\"",
|
|
dsCondition: "\"d\".\"cId\" = \"c\".\"id\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughCs), """
|
|
SELECT "d".* \
|
|
FROM "d" \
|
|
JOIN "c" ON "c"."id" = "d"."cId" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughBs), """
|
|
SELECT "d".* \
|
|
FROM "d" \
|
|
JOIN "c" ON "c"."id" = "d"."cId" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testBelongsToHasManyHasMany() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let b = belongsTo(B.self)
|
|
static let cs = hasMany(C.self, through: b, using: B.cs)
|
|
static let dsThroughCs = hasMany(D.self, through: cs, using: C.ds)
|
|
static let dsThroughB = hasMany(D.self, through: b, using: B.ds)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["bId"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let cs = hasMany(C.self)
|
|
static let ds = hasMany(D.self, through: cs, using: C.ds)
|
|
}
|
|
struct C: TableRecord {
|
|
static let ds = hasMany(D.self)
|
|
}
|
|
struct D: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
try db.create(table: "d") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("c")
|
|
}
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
}
|
|
|
|
try testHasManyWithThreeSteps(
|
|
db, bs: A.b, cs: A.cs, dsThroughCs: A.dsThroughCs, dsThroughBs: A.dsThroughB,
|
|
bsCondition: "\"b\".\"id\" = \"a\".\"bId\"",
|
|
csCondition: "\"c\".\"bId\" = \"b\".\"id\"",
|
|
dsCondition: "\"d\".\"cId\" = \"c\".\"id\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughCs), """
|
|
SELECT "d".* FROM "d" \
|
|
JOIN "c" ON "c"."id" = "d"."cId" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."id" = 1)
|
|
""")
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughB), """
|
|
SELECT "d".* FROM "d" \
|
|
JOIN "c" ON "c"."id" = "d"."cId" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."id" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testHasManyHasManyBelongsTo() throws {
|
|
struct A: TableRecord, EncodableRecord {
|
|
static let bs = hasMany(B.self)
|
|
static let cs = hasMany(C.self, through: bs, using: B.cs)
|
|
static let dsThroughCs = hasMany(D.self, through: cs, using: C.d)
|
|
static let dsThroughBs = hasMany(D.self, through: bs, using: B.ds)
|
|
func encode(to container: inout PersistenceContainer) {
|
|
container["id"] = 1
|
|
}
|
|
}
|
|
struct B: TableRecord {
|
|
static let cs = hasMany(C.self)
|
|
static let ds = hasMany(D.self, through: cs, using: C.d)
|
|
}
|
|
struct C: TableRecord {
|
|
static let d = belongsTo(D.self)
|
|
}
|
|
struct D: TableRecord {
|
|
}
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "a") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "d") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
try db.create(table: "b") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("a")
|
|
}
|
|
try db.create(table: "c") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("b")
|
|
t.belongsTo("d")
|
|
}
|
|
|
|
try testHasManyWithThreeSteps(
|
|
db, bs: A.bs, cs: A.cs, dsThroughCs: A.dsThroughCs, dsThroughBs: A.dsThroughBs,
|
|
bsCondition: "\"b\".\"aId\" = \"a\".\"id\"",
|
|
csCondition: "\"c\".\"bId\" = \"b\".\"id\"",
|
|
dsCondition: "\"d\".\"id\" = \"c\".\"dId\"")
|
|
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughCs), """
|
|
SELECT "d".* FROM "d" \
|
|
JOIN "c" ON "c"."dId" = "d"."id" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
try assertEqualSQL(db, A().request(for: A.dsThroughBs), """
|
|
SELECT "d".* FROM "d" \
|
|
JOIN "c" ON "c"."dId" = "d"."id" \
|
|
JOIN "b" ON ("b"."id" = "c"."bId") AND ("b"."aId" = 1)
|
|
""")
|
|
}
|
|
}
|
|
|
|
private func testHasManyWithThreeSteps<BsAssociation, CsAssociation, DsCsAssociation, DsBsAssociation>(
|
|
_ db: Database,
|
|
bs: BsAssociation,
|
|
cs: CsAssociation,
|
|
dsThroughCs: DsCsAssociation,
|
|
dsThroughBs: DsBsAssociation,
|
|
bsCondition: String,
|
|
csCondition: String,
|
|
dsCondition: String) throws
|
|
where
|
|
BsAssociation: Association,
|
|
CsAssociation: Association,
|
|
DsCsAssociation: Association,
|
|
DsBsAssociation: Association,
|
|
BsAssociation.OriginRowDecoder: TableRecord,
|
|
BsAssociation.OriginRowDecoder == CsAssociation.OriginRowDecoder,
|
|
BsAssociation.OriginRowDecoder == DsCsAssociation.OriginRowDecoder,
|
|
BsAssociation.OriginRowDecoder == DsBsAssociation.OriginRowDecoder
|
|
{
|
|
let A = CsAssociation.OriginRowDecoder.self
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs),
|
|
A.including(optional: dsThroughBs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs),
|
|
A.including(required: dsThroughBs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs),
|
|
A.joining(optional: dsThroughBs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs),
|
|
A.joining(required: dsThroughBs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).including(optional: bs),
|
|
A.including(optional: dsThroughBs).including(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".*, "d".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).including(optional: bs),
|
|
A.including(required: dsThroughBs).including(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).including(optional: bs),
|
|
A.joining(optional: dsThroughBs).including(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).including(optional: bs),
|
|
A.joining(required: dsThroughBs).including(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).including(required: bs),
|
|
A.including(optional: dsThroughBs).including(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).including(required: bs),
|
|
A.including(required: dsThroughBs).including(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).including(required: bs),
|
|
A.joining(optional: dsThroughBs).including(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).including(required: bs),
|
|
A.joining(required: dsThroughBs).including(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "b".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).joining(optional: bs),
|
|
A.including(optional: dsThroughBs).joining(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).joining(optional: bs),
|
|
A.including(required: dsThroughBs).joining(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).joining(optional: bs),
|
|
A.joining(optional: dsThroughBs).joining(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).joining(optional: bs),
|
|
A.joining(required: dsThroughBs).joining(optional: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).joining(required: bs),
|
|
A.including(optional: dsThroughBs).joining(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).joining(required: bs),
|
|
A.including(required: dsThroughBs).joining(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).joining(required: bs),
|
|
A.joining(optional: dsThroughBs).joining(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).joining(required: bs),
|
|
A.joining(required: dsThroughBs).joining(required: bs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
do {
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).including(optional: cs),
|
|
A.including(optional: dsThroughBs).including(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".*, "d".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).including(optional: cs),
|
|
A.including(required: dsThroughBs).including(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).including(optional: cs),
|
|
A.joining(optional: dsThroughBs).including(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).including(optional: cs),
|
|
A.joining(required: dsThroughBs).including(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).including(required: cs),
|
|
A.including(optional: dsThroughBs).including(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).including(required: cs),
|
|
A.including(required: dsThroughBs).including(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).including(required: cs),
|
|
A.joining(optional: dsThroughBs).including(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).including(required: cs),
|
|
A.joining(required: dsThroughBs).including(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "c".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).joining(optional: cs),
|
|
A.including(optional: dsThroughBs).joining(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).joining(optional: cs),
|
|
A.including(required: dsThroughBs).joining(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).joining(optional: cs),
|
|
A.joining(optional: dsThroughBs).joining(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
LEFT JOIN "b" ON \(bsCondition) \
|
|
LEFT JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).joining(optional: cs),
|
|
A.joining(required: dsThroughBs).joining(optional: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
for request in [
|
|
A.including(optional: dsThroughCs).joining(required: cs),
|
|
A.including(optional: dsThroughBs).joining(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.including(required: dsThroughCs).joining(required: cs),
|
|
A.including(required: dsThroughBs).joining(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".*, "d".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(optional: dsThroughCs).joining(required: cs),
|
|
A.joining(optional: dsThroughBs).joining(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
LEFT JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
for request in [
|
|
A.joining(required: dsThroughCs).joining(required: cs),
|
|
A.joining(required: dsThroughBs).joining(required: cs)]
|
|
{
|
|
try assertEqualSQL(db, request, """
|
|
SELECT "a".* \
|
|
FROM "a" \
|
|
JOIN "b" ON \(bsCondition) \
|
|
JOIN "c" ON \(csCondition) \
|
|
JOIN "d" ON \(dsCondition)
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|