WCDB之多表联合查询(五)

参考：

• IOS数据存储 之WCDB （二）WCDB.swift使用篇
• WCDB.swift-语言集成查询

• WCDB.swift-高级接口

• StudyWCDB
• StudentDBModel.swift
• ScoreDBModel.swift
• StudentDBWorker.swift

在关系型数据库中，为了数据维护方便，一般都有外键及关联表查询的情况，但是WCDB并不直接支持，可以使用执行原生sql的方式实现，或者使用业务代码多次调用实现

方案一：使用原生sql：

// MARK: - 连表查询
    func selectJoinOn1() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            // 2、多表查询使用JOIN、on
            let sqlStr =
"""
SELECT Score_Table.score, Score_Table.identifier, Students.name, Students.studentId FROM Score_Table
            JOIN Students on Score_Table.stuId=Students.studentId
""".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect().select(Column(named: sqlStr))
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).doubleValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).stringValue
                let val3 = handle.value(atIndex: 3).intValue
                print("selectJoinOn1 score:\(val0) identifier:\(val1) name:\(val2) studentId:\(val3)")
            }
        } catch {
            print(error)
        }
    }
    func selectJoinOn2() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            // 2、多表查询使用JOIN、on
            // 3、查询条件可以写在sql语句里
            let sqlStr =
"""
SELECT Score_Table.score, Score_Table.identifier, Students.name, Students.studentId FROM Score_Table
            JOIN Students on Score_Table.stuId=Students.studentId
WHERE Score_Table.stuId=345
""".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect().select(Column(named: sqlStr))
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).doubleValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).stringValue
                let val3 = handle.value(atIndex: 3).intValue
                print("selectJoinOn2 score:\(val0) identifier:\(val1) name:\(val2) studentId:\(val3)")
            }
        } catch {
            print(error)
        }
    }
    func selectJoinOn3() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            // 2、多表查询使用JOIN、on
            // 3、查询条件使用wcdb的方式设置
            let sqlStr =
"""
Score_Table.score, Score_Table.identifier, Students.name, Students.studentId FROM Score_Table
            JOIN Students on Score_Table.stuId=Students.studentId
""".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect()
            statementSelect.select(Column(named: sqlStr)).where(ScoreDBModel.Properties.stuId==345)
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).doubleValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).stringValue
                let val3 = handle.value(atIndex: 3).intValue
                print("selectJoinOn3 score:\(val0) identifier:\(val1) name:\(val2) studentId:\(val3)")
            }
        } catch {
            print(error)
        }
    }

方案二：使用wcdb的标准方式，根据业务关系每张表分别查询，然后再将数据关联在一起

func selectJoinOn4() {
        let db = Database(at: dbPath)
        do {
            // 先查询一张表
            let scoreList: [ScoreDBModel] = try db.getObjects(on: ScoreDBModel.Properties.all, fromTable: ScoreDBModel.tableName)
            // 将查询结果作为条件查询第二张表
            let stuIds: [Int] = scoreList.map { $0.stuId ?? 0 }
            let students: [StudentDBModel] = try db.getObjects(on: StudentDBModel.Properties.all, fromTable: StudentDBModel.tableName, where: StudentDBModel.Properties.studentId.in(stuIds))
            // 将两张表的查询结果根据业务关系关联在一起
            var studentsDict: [Int: StudentDBModel] = [:]
            for stu in students {
                studentsDict[stu.studentId ?? 0] = stu
            }
            var unionList: [(ScoreDBModel, StudentDBModel)] = []
            for score in scoreList {
                if let score_stu = studentsDict[score.stuId ?? 0] {
                    unionList.append((score, score_stu))
                }
            }
            // 使用数据
            for item in unionList {
                let score = item.0
                let score_stu = item.1
                print("selectJoinOn4 score:\(score.score ?? 0) identifier:\(score.identifier ?? 0) name:\(score_stu.name ?? "") studentId:\(score_stu.studentId ?? 0)")
            }
        } catch {
            print(error)
        }
    }
    func selectJoinOn5() {
        let db = Database(at: dbPath)
        do {
            // 先查询一张表
            let scoreList: [ScoreDBModel] = try db.getObjects(on: ScoreDBModel.Properties.all, fromTable: ScoreDBModel.tableName, where: ScoreDBModel.Properties.stuId==345)
            // 将查询结果作为条件查询第二张表
            let stuIds: [Int] = scoreList.map { $0.stuId ?? 0 }
            let students: [StudentDBModel] = try db.getObjects(on: StudentDBModel.Properties.all, fromTable: StudentDBModel.tableName, where: StudentDBModel.Properties.studentId.in(stuIds))
            // 将两张表的查询结果根据业务关系关联在一起
            var studentsDict: [Int: StudentDBModel] = [:]
            for stu in students {
                studentsDict[stu.studentId ?? 0] = stu
            }
            var unionList: [(ScoreDBModel, StudentDBModel)] = []
            for score in scoreList {
                if let score_stu = studentsDict[score.stuId ?? 0] {
                    unionList.append((score, score_stu))
                }
            }
            // 使用数据
            for item in unionList {
                let score = item.0
                let score_stu = item.1
                print("selectJoinOn5 score:\(score.score ?? 0) identifier:\(score.identifier ?? 0) name:\(score_stu.name ?? "") studentId:\(score_stu.studentId ?? 0)")
            }
        } catch {
            print(error)
        }
    }