D4V1D D4V1D - 6 months ago 24
SQL Question

How to filter OneToMany relationship' entity based on specific field when querying main entity

So I have this basic blog that contains articles (

AppBundle\Entity\Courrier
) and comments (
AppBundle\Entity\Reaction
). These two entities are bound together through a
OneToMany
relationship. Here are their definitions:

Courrier.php



<?php

namespace AppBundle\Entity;

class Courrier
{
// ...

/**
*
* @ORM\OneToMany(targetEntity="Reaction", mappedBy="courrier")
*/
private $reactions;


Reaction.php



<?php

namespace AppBundle\Entity;

class Reaction
{
const STATUS_ACCEPTED = 0;
const STATUS_PENDING = 1;
const STATUS_MODERATED = 2;
const STATUS_TRASHED = 3;

// ...

/**
* Thread of this comment
*
* @var Courrier
* @ORM\ManyToOne(targetEntity="Courrier", inversedBy="reactions")
* @ORM\JoinColumn(name="courrier_id", referencedColumnName="id")
*/
private $courrier;


with

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


My problem is that when I'm querying
AppBundle:Courrier
repository, I want to filter
AppBundle:Reaction
based on
$status
. That means doing something like :

$courrier = $doctrine->getRepository('AppBundle:Courrier')->findOneWithReactionsFiltered($slugCourrier, Reaction::STATUS_ACCEPTED)


So I built this very repository method and here it goes:

public function findOneWithReactionsFiltered($slug, $status)
{
return $this->createQueryBuilder('c')
->leftJoin('c.reactions', 'r')
->where('c.slug = :slug')
->andWhere('r.status = :status')
->setParameters([
'slug' => $slug,
'status' => $status,
])
->getQuery()
->getOneOrNullResult()
;
}


But the return value of this method (that is
$courrier
) is filled with
Courrier
entity with its
Reaction
s regardless of the
$status
.

I also dumped the SQL query :

SELECT c0_.id AS id0, c0_.name AS name1, c0_.slug AS slug2, c0_.envoi AS envoi3, c0_.intro AS intro4, c0_.courrier AS courrier5, c0_.reponse AS reponse6, c0_.published AS published7, c0_.like_count AS like_count8, c0_.recu AS recu9, c0_.image_id AS image_id10, c0_.categorie_id AS categorie_id11 FROM courrier c0_ LEFT JOIN reaction r1_ ON c0_.id = r1_.courrier_id WHERE c0_.slug = 'yaourt-cerise' AND r1_.status = 0;


Finally, here's a small dump of the table:

mysql> select r.id, r.status, c.slug from reaction as r left join courrier as c on c.id = r.courrier_id order by c.slug asc;
+-------+--------+------------------------------------+
| id | status | slug |
+-------+--------+------------------------------------+
| 15533 | 1 | yaourt-cerise |
| 15534 | 1 | yaourt-cerise |
| 15535 | 1 | yaourt-cerise |
| 15536 | 1 | yaourt-cerise |
| 15537 | 1 | yaourt-cerise |
| 15538 | 1 | yaourt-cerise |
| 15539 | 1 | yaourt-cerise |
| 15540 | 1 | yaourt-cerise |
| 15541 | 1 | yaourt-cerise |
| 15526 | 0 | yaourt-cerise |
| 15542 | 1 | yaourt-cerise |
| 15527 | 1 | yaourt-cerise |
| 15543 | 1 | yaourt-cerise |
| 15528 | 1 | yaourt-cerise |
| 15544 | 1 | yaourt-cerise |
| 15529 | 1 | yaourt-cerise |
| 15545 | 1 | yaourt-cerise |
| 15530 | 1 | yaourt-cerise |
| 15546 | 1 | yaourt-cerise |
| 15531 | 1 | yaourt-cerise |
+-------+--------+------------------------------------+


Can someone help?

Answer

You need to select both entities to hydrate then into the collection on query.

E.g. you need to add another select for the r alias:

->addSelect('r')

Then your collection will be correctly loaded and filtered from your query.

For more info see my answer below:

Filtering of associated entity collection in Symfony / Doctrine