Return total count of enteries using raw sql in Vapor

user_1327778 0
Viewed 344

I'm trying to route an incoming GET to return the following string:

"The total number of our models is 12"

where 12 is the actual number of entries of a specific model saved to the database.

Now one way to do this is to use the following:

func index(_ req: Request) throws -> Future<String> {
  return Model.query(on: req).all().map { models in
    return "The total number of our models is \(models.count)"
  }
}

This is the most documented and at the same time most inefficient way of doing this. I couldn't find any query that maps to "SELECT COUNT(*) FROM Model;"

So I resorted to writing my own raw SQL against the database. I've gotten this far but I can't figure out how to map [PostgreSQLColumn : PostgreSQLData] to Future<String>

  func index(_ req: Request) throws -> Future<String> {
    return req.withPooledConnection(to: .psql) { (conn) in
      conn.raw("SELECT COUNT(*) FROM MODEL").all()
          ///....something something 
    }
  }
vapor-fluent vapor swift
2 Answers

Instead of just all() and first() you could use all(decoding:) and first(decoding:) to decode returned raw rows

struct CountResult: Content {
    let count: Int64
}

func index(_ req: Request) throws -> Future<String> {
    req.withPooledConnection(to: .psql) { conn in
        conn.raw("SELECT COUNT(*) as count FROM MODEL").first(decoding: CountResult.self).map {
            $0?.count ?? 0
        }.map {
            "The total number of our models is \($0)"
        }
    }
}

Also I'd suggest to take a look at SwifQL and Bridges libraries to work with raw SQL in type-safe way.

With pure SwifQL

struct CountResult: Content {
    let count: Int64
}

func index(_ req: Request) throws -> Future<String> {
    req.withPooledConnection(to: .psql) { conn in
        let query = SwifQL
            .select(Fn.count(MyTable.table.*))
            .from(MyTable.table)
        return conn.raw(query)
            .first(decoding: CountResult.self)
            .map { $0?.count ?? 0 }
            .map {
                "The total number of our models is \($0)"
            }
    }
}

With SwifQL + Bridges

func index(_ req: Request) throws -> Future<String> {
    MyTable.query(on: .psql, on: req).count().map {
        "The total number of our models is \($0)"
    }
}
user_10010570

To return the count of rows you can do:

Model.query(on: req).count()

You can also apply filters to that query as well

user_18237050
Related