CODEWITHSUNDEEP
vaporvapor-fluent
Gargoyle
Gargoyle

Reputation: 10324

Doing joins with Fluent in a Vapor application

I'm struggling to figure out how to join my two tables together using fluent. In essence I want to run this SQL command:

SELECT p.name, o.amount, o.amount * p.amount total
FROM "OrderPoints" o 
INNER JOIN "Points" p ON o.points_id = p.id 
WHERE order_id = 10831

I've got my two models setup like so:

final class OrderPoint: Codable, PostgreSQLModel, Content, Migration {
    var id: Int? = nil
    var orderID: Order.ID
    var pointID: Point.ID
    var amount: Double = 0

    var point: Parent<OrderPoint, Point> {
        return parent(\.pointID)
    }
}

final class Point: Codable, PostgreSQLModel, Content, Migration {
    var id: Int? = nil
    var name: String
    var abbrev: String
    var amount: Double
}

So now in my controller I loop over all of the orders that I care about:

// SELECT * FROM orders WHERE "month" = '2018-05-01'
let orders = try Order.query(on: req).filter(\Order.month == month).all()

return orders.flatMap(to: View.self) { orders in
    let content = try orders.map { (order) -> OrderIndexContent in
        let values = try order.orderPoints.query(on: req).all()

and I think that gets me all the items from order_points for the current ID, but I am just not seeing how to join it with the Points model so that I can do the rest of the query (i.e. get the multiplied amount and the point name)

enter image description here

So in the way I think fluent refers to things, OrderPoints is not a pivot, as it's not a many to many.

This can't be right.

The following seems to be working, but there's no way this is the "right" way to do it as this would be massive amounts of extra SQL calls, right?

_ = try! order.orderPoints
              .query(on: req)
              .all()
              .map(to: Void.self) { pointsForOrder in
                  pointsForOrder.forEach { orderPoint in
                      _ = try! orderPoint.point.get(on: req).map(to: Void.self) {
                          print("\(order.id!) \(order.name) \($0.abbrev) \(orderPoint.pointID) = \(orderPoint.amount) = \($0.amount * orderPoint.amount)")
                      }
                  }
          }

Upvotes: 3

Views: 1867

Answers (1)

Jimmy
Jimmy

Reputation: 212

In order to retrieve all the Points referenced in the OrderPoints from an Order you would have to query Point and join OrderPoint on it. Then you can add a filter on OrderPoint filtering the order you want to query.

This would result in the following Fluent query:

Point.query(on: req)
    .join(field: \OrderPoint.pointID)
    .filter(OrderPoint.self, \OrderPoint.orderID == order.id!)
    .all()

This query would however only return an array of Points, so you would miss the information from OrderPoint as you pointed out in our discussion on Discord we would also have to decode OrderPoint, luckily Fluent has a nice method for this: .alsoDecode. So the final query would look like this:

Point.query(on: req)
    .join(field: \OrderPoint.pointID)
    .filter(OrderPoint.self, \OrderPoint.orderID == order.id!)
    .alsoDecode(OrderPoint.self)
    .all()

This would return a tuple containing both Point and OrderPoint

Upvotes: 6

Related Questions