用join进行选择中的学说子查询

    Membership:
      columns:
        id: { type: integer(4), notnull: true, unique: true, primary: true, autoincrement: true }
        organisation_id: { type: integer(4), notnull: true }
        membership_subcategory_id: { type: integer(4), notnull: true }
        start: { type: timestamp }
        end: { type: timestamp }
        amount: { type: decimal, size: 2 }
        amount_paid: { type: decimal, size: 2 }
        notes: { type: clob (16000000), notnull: false }
        payment_type: { type: string(20), notnull: true }
        order_id: { type: integer(4), notnull: false }
        payment_type: { type: string(20), notnull: false }
        active: { type: boolean, notnull: true }
        certificate_sent:  { type: timestamp, notnull: false }
        welcome_email_sent:  { type: timestamp, notnull: false }
      relations:
        MembershipSubcategory: { local: membership_subcategory_id, class: MembershipSubcategory, foreign: id }
        Organisation: { local: organisation_id }
        Order: { class: JosVmOrder, local: order_id, foreign: order_id }
        LatestMembership: { local: id, foreign: id }

    JosVmOrder:
      tableName: jos_vm_orders
      columns:
        order_id: { type: integer(4), primary: true, autoincrement: true }
        user_id: { type: integer(4), notnull: true }
        vendor_id: { type: integer(4), notnull: true }
        order_number: { type: string(32), notnull: false }
        user_info_id: { type: string(32), notnull: false }
        order_total: { type: \'decimal(15, 5)\', notnull: true }
        order_subtotal: { type: \'decimal(15, 5)\', notnull: false }
        order_tax: { type: \'decimal(10, 2)\', notnull: false }
        order_tax_details: { type: string(), notnull: true }
        order_shipping: { type: \'decimal(10, 2)\', notnull: false }
        order_shipping_tax: { type: \'decimal(10, 2)\', notnull: false }
        coupon_discount: { type: \'decimal(12, 2)\', notnull: true }
        coupon_code: { type: string(32), notnull: false }
        order_discount: { type: \'decimal(12, 2)\', notnull: true }
        order_currency: { type: string(16), notnull: false }
        order_status: { type: string(1), notnull: false }
        cdate: { type: integer(4), notnull: false }
        mdate: { type: integer(4), notnull: false }
        ship_method_id: { type: string(255), notnull: false }
        customer_note: { type: string(), notnull: true }
        ip_address: { type: string(15), notnull: true }
      relations:
        User: { class: JosUser, local: user_id, foreignAlias: OrderList }

    JosVmOrderHistory:
      tableName: jos_vm_order_history
      columns:
        order_status_history_id: { type: integer(4),primary: true, autoincrement: true }
        order_id: { type: integer(4) }
        order_status_code: { type: string(1) }
        date_added: { type: timestamp(25) }
        customer_notified: { type: integer(4), notnull: false }
        comments: { type: clob(16777777), notnull: false }
      relations:
        Order: { class: JosVmOrder, local: order_id, foreign: order_id }
        OrderStatus: { class: JosVmOrderStatus, local: order_status_code, foreign: order_status_code }

    JosVmOrderStatus:
      columns:
        order_status_id: { type: integer(4), primary: true, autoincrement: true }
        order_status_code: { type: string(1), fixed: true, notnull: true }
        order_status_name: { type: string(64), notnull: false }
        order_status_description: { type: string(), notnull: true }
        list_order: { type: integer(4), notnull: false }
        vendor_id: { type: integer(4), notnull: false }

$alias = $query->getRootAlias();

    $query->innerJoin (\"$alias.Order o\")
    ->select(\"o.id, o.*\")
    ->addSelect(\"(select os.order_status_name from JosVmOrderStatus os inner join os.JosVmOrderHistory oh on os.order_status_id=oh.order_status_id where oh.order_id=o.order_id order by oh.date_added desc limit 1)\");