GRDB Playgrounds

different-results.swift

// To run this playground, select and build the GRDBOSX scheme.

import GRDB
import PlaygroundSupport

//models

public struct User: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "user"
    public static let log = hasMany(StateLog.self)
    
    public var id: Int64?
    public var isActive: Bool = false
    
    init(id: Int64? = nil) {
        self.id = id
    }
    
    enum Columns {
        public static let id = Column(CodingKeys.id)
        public static let isActive = Column(CodingKeys.isActive)
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

public enum StateType: Int, DatabaseValueConvertible, Codable {
    case active = 0
    case inactive = 1
}

public struct StateLog: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "log"
    
    public var id: Int64?
    public var date: Date
    public var userId: Int64
    public var type: StateType
    
    public init(date: Date = Date(), userId: Int64, type: StateType) {
        self.date = date
        self.userId = userId
        self.type = type
    }
    
    enum Columns {
        public static let id = Column(CodingKeys.id)
        public static let date = Column(CodingKeys.date)
        public static let userId = Column(CodingKeys.userId)
        public static let type = Column(CodingKeys.type)
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}


extension User {
    static public var byDateRequest: QueryInterfaceRequest<User> {
        return User.filter(Columns.isActive == false)
            .joining(required: log
                .select([max(StateLog.Columns.date).forKey("maxDate")])
                .filter(StateLog.Columns.type == StateType.inactive.rawValue)
                .order([StateLog.Columns.date.desc])
            ).group([Columns.id])
    }
}

///////////////////////////////////


//database connection configuration

var configuration = Configuration()
configuration.trace = { print($0) }

var migrator = DatabaseMigrator()

migrator.registerMigration("tables") { database in
    try database.create(table: User.databaseTableName) { definition in
        definition.column(User.Columns.id.name, .integer).primaryKey(autoincrement: true)
        definition.column(User.Columns.isActive.name, .boolean)
    }
    
    try database.create(table: StateLog.databaseTableName) { definition in
        definition.column(StateLog.Columns.id.name, .integer)
            .primaryKey(autoincrement: true)
        definition.column(StateLog.Columns.date.name, .datetime).notNull()
        definition.column(StateLog.Columns.userId.name, .integer)
            .references(User.databaseTableName, onDelete: .restrict)
            .notNull()
        definition.column(StateLog.Columns.type.name, .integer).notNull()
    }
}

func prepareDatabase(name: String) throws -> DatabaseQueue {
    let url = playgroundSharedDataDirectory.appendingPathComponent("GRDB")
    try! FileManager.default.createDirectory(at: url, withIntermediateDirectories: true)
    try? FileManager.default.removeItem(at: url.appendingPathComponent("\(name).sqlite-wal"))
    try? FileManager.default.removeItem(at: url.appendingPathComponent("\(name).sqlite-shm"))
    try? FileManager.default.removeItem(at: url.appendingPathComponent("\(name).sqlite"))
    let databasePath = url.appendingPathComponent("\(name).sqlite").path
    print(databasePath)
    
    let queue = try DatabaseQueue(path: databasePath, configuration: configuration)
    try migrator.migrate(queue)
    
    return queue
}



////////////////////////////////////////////////////


func sample1() throws {
    let queue = try prepareDatabase(name: "result-6-5")
    
    try queue.write { db in
        try db.execute(sql: """
                                INSERT INTO user (id, isActive) VALUES (5, 0);
                                INSERT INTO user (id, isActive) VALUES (6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (31, '1568232006000', 5, 1); -- max 5
                                INSERT INTO log (id, date, userId, type) VALUES (20, '1568214000000', 5, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (21, '1568217600000', 5, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (22, '1568221200000', 5, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (23, '1568224800000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (24, '1568228400000', 6, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (25, '1568232000000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (32, '1568232007000', 6, 1); -- max 6
                                INSERT INTO log (id, date, userId, type) VALUES (26, '1568232001000', 5, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (28, '1568232003000', 6, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (29, '1568232004000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (30, '1568232005000', 5, 0);
                                """)
    }
    
    let result = try queue.read { try User.byDateRequest.fetchAll($0) }
    
    assert(result.count == 2)
    assert(result[0].id == 6)
    assert(result[1].id == 5)
    
    try runRaw(queue: queue)
}

func sample2() throws {
    let queue = try prepareDatabase(name: "result-5-6")
    
    try queue.write { db in
        try db.execute(sql: """
                                INSERT INTO user (id, isActive) VALUES (5, 0);
                                INSERT INTO user (id, isActive) VALUES (6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (20, '1568214000000', 5, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (21, '1568217600000', 5, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (22, '1568221200000', 5, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (23, '1568224800000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (24, '1568228400000', 6, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (25, '1568232000000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (26, '1568232001000', 5, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (28, '1568232003000', 6, 1);
                                INSERT INTO log (id, date, userId, type) VALUES (29, '1568232004000', 6, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (30, '1568232005000', 5, 0);
                                INSERT INTO log (id, date, userId, type) VALUES (31, '1568232006000', 5, 1); -- max 5
                                INSERT INTO log (id, date, userId, type) VALUES (32, '1568232005000', 6, 1); -- max 6
                                """)
    }
    
    let result = try queue.read { try User.byDateRequest.fetchAll($0) }

    assert(result.count == 2)
    assert(result[0].id == 5)
    assert(result[1].id == 6)
    
    try runRaw(queue: queue)
}

func runRaw(queue: DatabaseQueue) throws {
    let query1 = """
    SELECT "user".*, date, log.id
    FROM "user"
             JOIN "log" ON ("log"."userId" = "user"."id") AND ("log"."type" = 1)
    WHERE NOT "user"."isActive"
    GROUP BY "user"."id"
    ORDER BY "log"."date" DESC;
    """
    
    let query2 = """
    SELECT user.*, max(date) as maxDate, log.id
    FROM user
             JOIN log on user.id = log.userId AND log.type = 1
    WHERE user.isActive = 0
    GROUP BY user.id
    ORDER BY maxDate DESC;
    """
    
    let rows1 = try queue.read { try Row.fetchAll($0, sql: query1) }
    let rows2 = try queue.read { try Row.fetchAll($0, sql: query2) }
    
    print("==========")
    print(rows1)
    print(rows2)
    print("==========")
}


try sample1()
try sample2()

grdb-nested-select.swift

// To run this playground, select and build the GRDBOSX scheme.

import GRDB

public struct User: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "user"
    
    public var id: Int64?
    
    init(id: Int64? = nil) {
        self.id = id
    }
    
    enum Columns {
        public static let id = Column(CodingKeys.id)
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

public struct Diff: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "diff"
    
    public var id: Int64?
    public var date: Date
    public var userId: Int64
    public var type: Int
    
    public init(date: Date = Date(), userId: Int64, type: Int) {
        self.date = date
        self.userId = userId
        self.type = type
    }
    
    enum Columns {
        public static let id = Column(CodingKeys.id)
        public static let date = Column(CodingKeys.date)
        public static let userId = Column(CodingKeys.userId)
        public static let type = Column(CodingKeys.type)
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

extension User {
    public static let diff = hasMany(Diff.self)
}

extension Diff {
    public static let user = hasOne(User.self)
}

var configuration = Configuration()
configuration.trace = { print($0) }

var migrator = DatabaseMigrator()

migrator.registerMigration("initial") { database in
    try database.create(table: User.databaseTableName) { definition in
        definition.column(User.Columns.id.name, .integer).primaryKey(autoincrement: true)
    }
    
    try database.create(table: Diff.databaseTableName) { definition in
        definition.column(Diff.Columns.id.name, .integer)
            .primaryKey(autoincrement: true)
        definition.column(Diff.Columns.date.name, .datetime).notNull()
        definition.column(Diff.Columns.userId.name, .integer)
            .references(User.databaseTableName, onDelete: .restrict)
            .notNull()
        definition.column(Diff.Columns.type.name, .integer).notNull()
    }
}

migrator.registerMigration("data") { database in
    var user = User(id: 1)
    try user.save(database)
    
    var diff1 = Diff(date: Date(timeIntervalSinceNow: -10000), userId: 1, type: 0)
    var diff2 = Diff(date: Date(timeIntervalSinceNow: -9000), userId: 1, type: 1)
    
    try diff1.save(database)
    try diff2.save(database)
}

let queue = DatabaseQueue(configuration: configuration)
try migrator.migrate(queue)

let query = """
SELECT user.* FROM user
JOIN diff as d1 on d1.userId = user.id
WHERE d1.type = 1 AND d1.date > (
    SELECT date FROM diff as d2
    WHERE d2.type = 0 AND d1.userId = d2.userId
    ORDER BY date DESC
    LIMIT 1
);
"""

assert(try! queue.read({ try User.fetchAll($0, sql: query) }).count == 1, "Must be 1 match")
try queue.write({ database in
    var diff = Diff(date: Date(timeIntervalSinceNow: -8000), userId: 1, type: 0)
    try diff.save(database)
})
assert(try! queue.read({ try User.fetchAll($0, sql: query) }).count == 0, "Must be 0 matches")

let d1Alias = TableAlias(name: "d1")
let d2Alias = TableAlias(name: "d2")

let queryIR: QueryInterfaceRequest<User> = User.including(required:
    User.diff.filter(Diff.Columns.type == 1 /*&& Diff.Columns.date > "SELECT date..."*/).select([]).aliased(d1Alias)
    )


let result = try queue.read {try queryIR.fetchAll($0) }
print(result)

groue/GRDB.swift#593