Doctrine2 QueryBuilder select entity and count of associated entities

Question

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.

I think about 2 solutions. First is to keep number of products in order table then You will avoid complicated and slow query. Second is to use DQL with DTO object — kskaradzinski
– kskaradzinski, Commented Dec 21, 2016 at 20:12
@skowron-line this is not viable, because in next step I'll need to filter the "count" by status of the product and thus it will get updated really frequently. I'd like to avoid unnecessary updates to count field in order. Also it goes against normalization. — pzaj
– pzaj, Commented Dec 22, 2016 at 6:40
@FrankB I don't fetch them from database, only need their count. I'd like to avoid fetching 20~ products for order when I need to fetch 15~ orders. — pzaj
– pzaj, Commented Dec 22, 2016 at 6:42
It's possible 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. Let me know if you need a code snippet. — Jeet
– Jeet, Commented Dec 22, 2016 at 7:06

Jeet · Accepted Answer · 2016-12-22 10:31:07Z

2

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.

answered Dec 22, 2016 at 10:31

Jeet

1,5871 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pzaj Over a year ago

Works as expected, this is the right solution I was looking for.

Collectives™ on Stack Overflow

Doctrine2 QueryBuilder select entity and count of associated entities

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related