I tried to make the most basic example that I could think of for my problem. I have a Course model and a many-to-many table to User that also stores some extra properties (the progress in the example below).
import FluentPostgreSQL
import Vapor
final class Course: Codable, PostgreSQLModel {
var id: Int?
var name: String
var teacherId: User.ID
var teacher: Parent<Course, User> {
return parent(\.teacherId)
}
init(name: String, teacherId: User.ID) {
self.name = name
self.teacherId = teacherId
}
}
struct CourseUser: Pivot, PostgreSQLModel {
typealias Left = Course
typealias Right = User
static var leftIDKey: LeftIDKey = \.courseID
static var rightIDKey: RightIDKey = \.userID
var id: Int?
var courseID: Int
var userID: UUID
var progress: Int
var user: Parent<CourseUser, User> {
return parent(\.userID)
}
}
Now, when I return a Course object, I want the JSON output to be something like this:
{
"id": 1,
"name": "Course 1",
"teacher": {"name": "Mr. Teacher"},
"students": [
{"user": {"name": "Student 1"}, progress: 10},
{"user": {"name": "Student 2"}, progress: 60},
]
}
Instead of what I would normally get, which is this:
{
"id": 1,
"name": "Course 1",
"teacherID": 1,
}
So I created some extra models and a function to translate between them:
struct PublicCourseData: Content {
var id: Int?
let name: String
let teacher: User
let students: [Student]?
}
struct Student: Content {
let user: User
let progress: Int
}
extension Course {
func convertToPublicCourseData(req: Request) throws -> Future<PublicCourseData> {
let teacherQuery = self.teacher.get(on: req)
let studentsQuery = try CourseUser.query(on: req).filter(\.courseID == self.requireID()).all()
return map(to: PublicCourseData.self, teacherQuery, studentsQuery) { (teacher, students) in
return try PublicCourseData(id: self.requireID(),
name: self.name,
teacher: teacher,
students: nil) // <- students is the wrong type!
}
}
}
Now, I am almost there, but I am not able to convert studentsQuery from EventLoopFuture<[CourseUser]> to EventLoopFuture<[Student]>. I tried multiple combinations of map and flatMap, but I can't figure out how to translate an array of Futures to an array of different Futures.
The logic you're looking for will look like this
extension Course {
func convertToPublicCourseData(req: Request) throws -> Future<PublicCourseData> {
return teacher.get(on: req).flatMap { teacher in
return try CourseUser.query(on: req)
.filter(\.courseID == self.requireID())
.all().flatMap { courseUsers in
// here we should query a user for each courseUser
// and only then convert all of them into PublicCourseData
// but it will execute a lot of queries and it's not a good idea
}
}
}
}
I suggest you to use the SwifQL lib instead to build a custom query to get needed data in one request 🙂
You could mix Fluent's queries with SwifQL's in case if you want to get only one course, so you'll get it in 2 requests:
struct Student: Content {
let name: String
let progress: Int
}
extension Course {
func convertToPublicCourseData(req: Request) throws -> Future<PublicCourseData> {
return teacher.get(on: req).flatMap { teacher in
// we could use SwifQL here to query students in one request
return SwifQL.select(\CourseUser.progress, \User.name)
.from(CourseUser.table)
.join(.inner, User.table, on: \CourseUser.userID == \User.id)
.execute(on: req, as: .psql)
.all(decoding: Student.self).map { students in
return try PublicCourseData(id: self.requireID(),
name: self.name,
teacher: teacher,
students: students)
}
}
}
}
If you want to get a list of courses in one request you could use pure SwifQL query.
I simplified desired JSON a little bit
{
"id": 1,
"name": "Course 1",
"teacher": {"name": "Mr. Teacher"},
"students": [
{"name": "Student 1", progress: 10},
{"name": "Student 2", progress: 60},
]
}
first of all let's create a model to be able to decode query result into it
struct CoursePublic: Content {
let id: Int
let name: String
struct Teacher:: Codable {
let name: String
}
let teacher: Teacher
struct Student:: Codable {
let name: String
let progress: Int
}
let students: [Student]
}
Ok now we are ready to build a custom query. Let's build it in some request handler function
func getCourses(_ req: Request) throws -> Future<[CoursePublic]> {
/// create an alias for student
let s = User.as("student")
/// build a PostgreSQL's json object for student
let studentObject = PgJsonObject()
.field(key: "name", value: s~\.name)
.field(key: "progress", value: \CourseUser.progress)
/// Build students subquery
let studentsSubQuery = SwifQL
.select(Fn.coalesce(Fn.jsonb_agg(studentObject),
PgArray(emptyMode: .dollar) => .jsonb))
.from(s.table)
.where(s~\.id == \CourseUser.userID)
/// Finally build the whole query
let query = SwifQLSelectBuilder()
.select(\Course.id, \Course.name)
.select(Fn.to_jsonb(User.table) => "teacher")
.select(|studentsSubQuery| => "students")
.from(User.table)
.join(.inner, User.table, on: \Course.teacherId == \User.id)
.join(.leftOuter, CourseUser.table, on: \CourseUser.teacherId == \User.id)
.build()
/// this way you could print raw query
/// to execute it in postgres manually
/// for debugging purposes (e.g. in Postico app)
print("raw query: " + query.prepare(.psql).plain)
/// executes query with postgres dialect
return query.execute(on: req, as: .psql)
/// requests an array of results (or use .first if you need only one first row)
/// You also could decode query results into the custom struct
.all(decoding: CoursePublic.self)
}
Hope it will help you. There may be some mistakes in the query cause I wrote it without checking 🙂 You can try to print a raw query to copy it and execute in e.g. Postico app in postgres directly to understand what's wrong.