How to count fileds in a database query?

Question

I got two tables, join them and now i just want to count one of them

Select(count(myOtherTable.name), from: myTable).join(myOtherTable).on(myTable.name == myOtherTable.someName)

Executing that query I get

The operation couldn't be completed. (SwiftKuery:QueryError 2.)

Some ideas what my mistake is?

Answer 1

I suspect he issue is that you should be using count(myOtherTable.someName).

Below is sample code that demonstrates the query in your question functioning as expected based on a database schema derived from it.

import Foundation
import Dispatch

import SwiftKueryMySQL
import SwiftKuery

let semaphore = DispatchSemaphore(value: 0)


func handleError(_ error: String) {
    print(error)
    semaphore.signal()
}

class Person : Table {
    let tableName = "infos"
    let name = Column("name", String.self)
}

class Customer : Table {
    let tableName = "customers"
    let name = Column("name", String.self)
    let nickname = Column("nickname", String.self)
}

let mySQLPool = MySQLConnection.createPool(user: "root", database: "playground", poolOptions: ConnectionPoolOptions(initialCapacity: 1, maxCapacity: 10))

let infos = Person()
let customers = Customer()

let insertInfo = Insert(into: infos, rows: [["personone"],["persontwo"],["personthree"]])
let insertCustomer = Insert(into: customers, rows: [["p2","persontwo"]])

let select = Select(count(customers.name), from: infos).join(customers).on(infos.name == customers.nickname)

mySQLPool.getConnection() { connection, error in
    guard let connection = connection else {
        guard let error = error else {
            return handleError("Unable to get connection: Unknown error")
        }
        return handleError("Unable to get connection: \(error.localizedDescription)")
    }

    infos.create(connection: connection) { result in
        guard result.success else {
            guard let error = result.asError else {
                return handleError("Unable to create table: Unknown error")
            }
            return handleError("Unable to create table: \(error.localizedDescription)")
        }

        customers.create(connection: connection) { result in
            guard result.success else {
                guard let error = result.asError else {
                    return handleError("Unable to create second table: Unknown error")
                }
                return handleError("Unable to create table: \(error.localizedDescription)")
            }

            connection.execute(query: insertInfo) { result in
                guard result.success else {
                    guard let error = result.asError else {
                        return handleError("Unable to insert info: Unknown error")
                    }
                    return handleError("Unable to insert info: \(error.localizedDescription)")
                }

                connection.execute(query: insertCustomer) { result in
                    guard result.success else {
                        guard let error = result.asError else {
                            return handleError("Unable to insert customer: Unknown error")
                        }
                        return handleError("Unable to insert customer: \(error.localizedDescription)")
                    }

                    connection.execute(query: select) { result in
                        guard result.success else {
                            guard let error = result.asError else {
                                return handleError("Unable to create table: Unknown error")
                            }
                            return handleError("Unable to create table: \(error.localizedDescription)")
                        }

                        result.asRows() { rows, error in
                            guard let rows = rows else {
                                guard let error = error else {
                                    return handleError("Unable to get rows: Unknown error")
                                }
                                return handleError("Unable to get rows: \(error.localizedDescription)")
                            }

                            for row in rows {
                                print("row: \(row)")
                            }
                            semaphore.signal()
                        }
                    }
                }
            }
        }
    }
}

semaphore.wait()