CODEWITHSUNDEEP
symfonydoctrine-ormquery-builder
pzaj
pzaj

Reputation: 1092

Doctrine2 QueryBuilder select entity and count of associated entities

I'm having a huge problem with ORM QueryBuilder. What I need to do is: I need to fetch order with count of its products and plenty of associated entities (associated with order), but I assume they're not relevant here. I also need to order result by that count.

Could anyone give me an example of how this can be achieved? I would like to avoid "inline" DQLs if possible.

Upvotes: 0

Views: 3986

Answers (1)

Jeet
Jeet

Reputation: 1587

You can get data via Doctrine Query Builder.

You are supposed to left join products from Order and then group by order id. You can have COUNT(product.id) in your select statement and use the alias in order by clause to make your orders sorted. Below is a small code snippet from Repository.

/**
 * @return \Doctrine\ORM\Query
 */
public function getHotelAndRoomType()
{
    $qb = $this->createQueryBuilder('order')
        ->select('partial order.{id, orderId} as order, count(product.id) as total_products_in_order')
        ->leftJoin('AppBundle:Product', 'product', 'WITH', 'product.order = order.id')
        ->groupBy('order.id')
        ->orderBy('total_products_in_order', 'DESC')
    ;

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

Note : Code not tested.

Upvotes: 2

Related Questions