使用Doctrine过滤相关表上的行

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.

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

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.