sql - Typeorm leftJoinAndSelect 与 subQuery

Question

我这里有一个很大的问题。我有三张桌子：

@Entity()
Vessel {
  @OneToMany()
  workOrders: WorkOrder[];
}

@Entity()
WorkOrder {
  @ManyToOne()
  vessel: Vessel;

  @OneToOne()
  order: Order;
}

@Entity()
Order {
  @OneToOne()
  workOrder: WorkOrder

  @Column()
  paid: Boolean
}

我需要进行查询，以获取所有船只及其 workOrders 及其订单。仅当订单已付款时才应选择 workOrder：false。因此，如果船只没有具有 order.paid = false 的 workOrders，它仍应被选中，但有一个空的 workOrders 数组。

数据看起来像这样：

vessels: [
  {
    id: 123,
    workOrders: [] // or null, or not selected, doesn't really matter
  },
  {
    id: 1234,
    workOrders: [
        {
          id: 99,
          order: {
            id: 12345,
            paid: false
          }
        },
        {
          id: 88,
          order: {
            id: 321,
            paid: false
          }
        },
      ]
  }
]

我试过这个：

const vessels = await getConnection()
            .createQueryBuilder()
            .select('vessel')
            .from('vessel', 'vessel')
            .leftJoinAndSelect(
                'vessel.workOrders',
                subQuery =>
                    subQuery
                        .select('workOrder.*')
                        .from(WorkOrder, 'workOrder')
                        .leftJoinAndSelect('workOrder.order', 'order')
                        .where('order.isInvoiced = :isInvoiced', { isInvoiced: false }),
                'workOrders',
                'workOrders.vesselId = vessel.id'
            )
            .getMany();

这会生成以下 SQL：

SELECT 
    "vessel"."id" AS "vessel_id", 
    "vessel"."vesselName" AS "vessel_vesselName", 
    "vessel"."isDeactivated" AS "vessel_isDeactivated", 
    "vessel"."externalID" AS "vessel_externalID", 
    "vessel"."externalDepartment" AS "vessel_externalDepartment", 
    "vessel"."operationalPlan" AS "vessel_operationalPlan", 
    "vessel"."planLastUpdated" AS "vessel_planLastUpdated", 
    "vessel"."phoneNumber" AS "vessel_phoneNumber", 
    "vessel"."email" AS "vessel_email", 
    "vessel"."info" AS "vessel_info", 
    "vessel"."vesselPictureId" AS "vessel_vesselPictureId", 
    "vessel"."categoryId" AS "vessel_categoryId", 
    "vessel"."departmentId" AS "vessel_departmentId", 
    "vessel"."externalClientId" AS "vessel_externalClientId", 
    "workOrders".* 
FROM 
    "vessel" "vessel" 
    LEFT JOIN (
        SELECT 
            "order"."id" AS "order_id", 
            "order"."orderDate" AS "order_orderDate", 
            "order"."isInvoiced" AS "order_isInvoiced", 
            "order"."isApproved" AS "order_isApproved", 
            "order"."isEmergency" AS "order_isEmergency", 
            "order"."isDisease" AS "order_isDisease", 
            "order"."description" AS "order_description", 
            "order"."when" AS "order_when", 
            "order"."precautions" AS "order_precautions", 
            "order"."info" AS "order_info", 
            "order"."orderNumber" AS "order_orderNumber", 
            "order"."customerId" AS "order_customerId", 
            "order"."locationId" AS "order_locationId", 
            "order"."customerUserId" AS "order_customerUserId", 
            "order"."categoryId" AS "order_categoryId", 
            "order"."orderStatusId" AS "order_orderStatusId", 
            "order"."departmentId" AS "order_departmentId", 
            workOrder.* 
        FROM 
            "work_order" "workOrder" 
            LEFT JOIN "order" "order" ON "order"."id" = "workOrder"."orderId" 
        WHERE 
            "order"."isInvoiced" = 0
    ) "workOrders" ON workOrders.vesselId = "vessel"."id"

这没有用。但是，我确实使用 getRawMany 获得了正确的数据，但结果如下：

[
  {
    id: null,          // workOrder id
    order_id: null,
    paid: null,
    vessel_id: 123
  },
  {
    id: 99,            // workOrder id
    order_id: 12345,
    order_paid: false,
    vessel_id: 1234
  },
  {
    id: 88,            // workOrder id
    order_id: 321,
    order_paid: false,
    vessel_id: 1234
  },
]

我怎样才能得到我想要的数据，按照我想要的格式？

标签： sqlnode.jssql-serverexpresstypeorm