Filter rows on related table using Doctrine

Question

I'm very new to Doctrine/Symfony and I'm pulling my hair because I can't get how to do this.

I have two tables related by one foreign key.

Product: id, name, stock
Order: id, date, productId, quantity, sent

I can get all the orders of one product with $produt->getOrders() but I'd like to get Only those which are pending to sent.

How should I do it?.

Thanks.

It would be good to know, what your Symfony/ Doctrine Entities are looking like. Because of the ORM Framework Doctrine you don't have to care about your database representation, but of your Entities. With Doctrine you can use your namespace and Entity attributes to create queries. — kinske, Aug 03 '16 at 13:23

score 0 · Answer 1 · edited May 23 '17 at 12:25

If You want all orders for many products You can use QueryBuilder:

$this->createQueryBuilder('order')
->join('order.productId', 'order')
->where('order.sent = :sentStatus')->setParameter('sentStatus', true)
->getQuery()
->getResult();

If You already have product object/entity and want to select orders per product:

$this->createQueryBuilder('order')
->join('order.productId', 'order')
->where('order.sent = :sentStatus')->setParameter('sentStatus', true)
->where('product = :productEntity')->setParameter('productEntity', $productEntity)
->getQuery()
->getResult();

If You don't want to use QueryBuilder You can loop, or filter orders collection: details

Read about Lazy Loading and it impact to performance

kinske · Answer 2 · 2016-08-03T13:15:04.890

You can do this in two approaches:

First is to use the QueryBuilder like this (which I would recommend):

class SomeServiceOrController() { 

    function getOrdersPending($productId) {

        return $this->getEntityManager()->createQueryBuilder()
            ->select('order')
            ->from('\AppBundle\Order', 'order') // or whatever your namespace your Entity is
            ->join('order.productId', 'product')
            ->where('order.productId =: productParam')
            ->andWhere('order.sent = :sentParam')
            ->setParameter('productParam', $productId)
            ->setParameter('sentParam', 'pending')
            ->getQuery()
            ->getResult;
    }
}

See the reference here.

Second is the DQL (Doctrine Query Language). See this reference for usage. It's pretty much like SQL with making usage of your php namespace.

Filter rows on related table using Doctrine

2 Answers2