mysql - Doctrine2的QueryBuilder（子查询）怎么写

Question

我不知道如何使用 SubQuery 编写 QueryBuilder。

select * from dtb_order o_1
inner join dtb_order_item oi_1 on oi_1.order_id = o_1.id
inner join dtb_product p_1 on p_1.id = oi_1.product_id
inner join dtb_product_class pc on pc.product_id = p_1.id
inner join dtb_product_customize_category pcc on pcc.product_id = p_1.id
inner join dtb_customize_category cc on cc.id = pcc.customize_category_id

where o_1.id = (

    select o_2.id
    from dtb_order o_2
    inner join dtb_order_item oi_2 on oi_2.order_id = o_2.id
    inner join dtb_product p_2 on p_2.id = oi_2.product_id
    where p_1.id = p_2.id
    order by o_2.create_date desc
    LIMIT 1
)

and cc.id = 1
limit 6

我写了它作为尝试。

public function getQuery()
{
    $qb = $this->createQueryBuilder('o_1');

    $sub_query = $this->createQueryBuilder('o_2')
                    ->leftJoin('o_2.OrderItems', 'oi_2')
                    ->leftJoin('oi_2.Product', 'p_2')
                    ->where('p_1.id = p_2.id')
                    ->orderBy('o_2.create_date','desc')
                    ->setMaxResults(1)
                    ->getDQL();

    $qb
        ->leftJoin('o_1.OrderItems', 'oi')
        ->leftJoin('oi.Product', 'p_1')
        ->leftJoin('p_1.ProductClasses', 'pc')
        ->leftJoin('p_1.ProductCustomizeCategory', 'pcc')
        ->leftJoin('pcc.CustomizeCategory', 'cc')
        ->where('o_1.id = (:sub_query)')
        ->setParameter('sub_query',$sub_query);

    return $qb->getQuery()->getResult();
}

没有错误，但我无法获取我正在寻找的数据...... getSQL（）确认“-> where（'o_1.id =（：sub_query）'）”的部分变为“（？） ”。请指教...

谢谢。

标签： mysqldoctrinequery-builder