在Vapor应用程序中与Fluent进行连接 [英] Doing joins with Fluent in a Vapor application

问题描述

我正在努力弄清楚如何使用流利的方法将我的两个表连接在一起.本质上，我想运行以下SQL命令:

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()

，我认为这可以从order_points中获取当前ID的所有项目，但我只是没有看到如何将其与Points模型结合使用，以便我可以完成其余的查询(即，获取乘数和点名称)

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)

• 一个Order具有多个OrderPoint项目.
• 一个OrderPoint具有一个单个 Point项.
• Point可能指向许多OrderPoint项目.

• An Order has multiple OrderPoint items.
• An OrderPoint has a single Point item.
• A Point could point to many OrderPoint items.

因此，以我认为流利的方式指事物的方式，OrderPoints不是 枢轴，因为它不是很多对很多.

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

以下内容似乎有效，但是这不可能是正确"的方法，因为这将导致大量额外的SQL调用，对吗?

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)")
                      }
                  }
          }

推荐答案

为了从Order检索OrderPoint中引用的所有Point，您必须查询Point并加入OrderPoint就可以了.然后，您可以在OrderPoint上添加一个过滤器，以过滤您要查询的顺序.

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.

这将导致以下Fluent查询:

This would result in the following Fluent query:

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

但是，此查询仅返回Point的数组，因此您会错过OrderPoint中的信息，正如您在Discord讨论中所指出的那样，我们还必须解码OrderPoint，幸运的是Fluent有一个一个不错的方法:.alsoDecode.因此，最终查询将如下所示:

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()

这将返回一个同时包含Point和OrderPoint

This would return a tuple containing both Point and OrderPoint

这篇关于在Vapor应用程序中与Fluent进行连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！