I have the following DB/Entity Structure. So a bug can have multiple products. I don't get the expected behaviour in a script if i use different queries (different join condition). I hope its easier to understand with the following code example.
Prepare Database:
INSERT INTO `bug` (`bug_id`, `description`, `status`) VALUES
(1, 'Entity manager doesnt work as expected', 'open'),
(2, 'Dotrine ORM issue', 'closed'),
(3, 'Another bug', 'closed');
INSERT INTO `product` (`product_id`, `name`) VALUES
(1, 'Doctrine 2'),
(2, 'Zend Framwork 2'),
(2, 'Another product');
INSERT INTO `bug_product` (`bug_id`, `product_id`) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 1),
(3, 3);
Repository:
class BugRepository extends \Doctrine\ORM\EntityRepository
{
// here we want all bugs with only one product e.g. for a product overview
public function findFilteredByProduct($productId)
{
$qb = $this->createQueryBuilder('bug')
->addSelect('product')
->join('bug.product', 'product')
->where('product = :productid')
->setParameter('productid', $productId);
$query = $qb->getQuery();
return $query->getResult();
}
// here we want all products for a bug, e.g. for bug details
public function findWithProduct($bugId)
{
$qb = $this->createQueryBuilder('bug')
->addSelect('product')
->join('bug.product', 'product')
->where('bug.bugId = :bugId')
->setParameter('bugId', $bugId);
$query = $qb->getQuery();
return $query->getResult();
}
}
Controller:
class IndexController extends \Zend\Mvc\Controller\AbstractActionController
{
public function testAction()
{
// assumed that $repoBug is initialized
// only bugs which have an association with product #1
$bugs = $repoBug->findFilteredByProduct(1);
/* @var $bug \Bug\Entity\Bug*/
foreach ($bugs as $bug) {
echo 'Bug #' . $bug->getBugId() . '<br/>';
/* @var $p \Bug\Entity\Product */
foreach ($bug->getProduct() as $p) { // here only product #1 for all bugs available, thats fine
echo '#' . $p->getProductId() . ' ' . $p->getName() . '<br/>';
}
}
// get bug #1 with all associated products
$bug1 = $repoBug->findWithProduct(1);
$bug1 = reset($bug1);
/* @var $p \Bug\Entity\Product */
foreach ($bug1->getProduct() as $p) { // here also only product #1 available, thats wrong, it should also product #2 available e.g. for bug #1
echo '#' . $p->getProductId() . ' ' . $p->getName() . '<br/>';
}
// some other stuff
}
}
Executed Queries:
findFilteredByProduct(1):
SELECT
b0_.bug_id AS bug_id0,
b0_.description AS description1,
b0_. STATUS AS status2,
p1_.product_id AS product_id3,
p1_. NAME AS name4
FROM
bug b0_
INNER JOIN bug_product b2_ ON b0_.bug_id = b2_.bug_id
INNER JOIN product p1_ ON p1_.product_id = b2_.product_id
WHERE
p1_.product_id = ?
findWithProduct(1):
SELECT
b0_.bug_id AS bug_id0,
b0_.description AS description1,
b0_. STATUS AS status2,
p1_.product_id AS product_id3,
p1_. NAME AS name4
FROM
bug b0_
INNER JOIN bug_product b2_ ON b0_.bug_id = b2_.bug_id
INNER JOIN product p1_ ON p1_.product_id = b2_.product_id
WHERE
b0_.bug_id = ?
Description:
On the first query (findFilteredByProduct(1)), i get bug entities with only one product which has the product id 1. No lazy loading is used. If i want now to get a bug with all related products (findWithProduct(1)), it doesnt work. The bug entity has e.g. two products #1 and #2, but the entity manager doesn't add the second product to the bug entity which was retrieved before.
One solution is to detach the bug entity before retrieve them with second query but in a huge application, i doesn't know if the entity already exists before or if all products are availabe. I think this is a common use case independent of this example and i don't know how to resolve this issue. I dont want always use lazy loading e.g. for performance. If the entity manager would add the second product to the bug entity, i could use the matching() function of the collection to only retrieve the product with id 1.
Is this a bug in doctrine orm 2.3.4 (also 2.4.0 beta)? What can i do without clear the entity manager or to detach the bug entity from first query?