Symfony2 ManyToMany在一个查询中

Question

I have 2 entities related ManyToMany

class Product
{

/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=100, unique=true)
 */
private $name;

/**
 * @ORM\ManyToMany(targetEntity="ProductTransfer", inversedBy="product")
 * @ORM\JoinTable(name="products_transfers")
 */
private $transfers;

and second one

class ProductTransfer
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="stock_from_id", type="integer")
 */
private $stockFromId;

/**
 * @var int
 *
 * @ORM\Column(name="stock_to_id", type="integer")
 */
private $stockToId;

/**
 * @var int
 *
 * @ORM\Column(name="product_id", type="integer")
 */
private $productId;

/**
 * @ORM\ManyToMany(targetEntity="Product",mappedBy="transfers")
 */
private $product;

All works great, additional table were created by Doctrine.
But when I'm trying to get all rows from ProductTransfers I see in profiler that each row need one single query with 2 joins.
So for 5000 products it will be really many queries.

Is there any way to get them all in one query like in "clean SQL"? If there is no way to do it with Doctrine, what is best way to achieve that?

Like:

SELECT * FROM product_transfer pt
LEFT JOIN products_transfers pts ON pt.product_id=pts.product_id
LEFT JOIN product p ON pts.product_id=p.id;

edit: created method in repository

public function loadTransfersByStock($stockId)
{

    $q = $this->getEntityManager()
        ->createQuery(
            'SELECT pt,p FROM AppBundle:ProductTransfer pt
            LEFT JOIN AppBundle:Product p WITH pt.productId=p.id
            WHERE pt.stockToId = :stockId'
        );

    return $q->setParameter('stockId', $stockId)->getResult();
}

and then result is

ProductsController.php on line 495:
array:3 [▼
  0 => ProductTransfer {#1071 ▶}
  1 => Product {#1084 ▶}
  2 => ProductTransfer {#1099 ▶}
]

Answer 1

By default Doctrine uses lazy loading in object relations - the rows for the relationships are only loaded when you try to access them. If you know you're going to be needing all the related rows, try changing to eager fetching.

/**
 * @ORM\ManyToMany(targetEntity="ProductTransfer", inversedBy="product", fetch="EAGER")
 * @ORM\JoinTable(name="products_transfers")
 */