Find entity with relation collection all match value

Question

In my Symfony project, I have an "ExerciceComptable" and a "DocumentAttendu" entities. There is a relation in ExerciceComptable that reference DocumentAttendu (OneToMany). In DocumentAttendu, I have a property named "recu" which is a boolean.

I need to retrieve all "ExerciceComptable" that are completed, meaning that all "DocumentAttendu" for an "ExerciceComptable" have the property "recu" set to true.

How can I achieve that ?

ExerciceComptable

#[ORM\OneToMany(mappedBy: 'exercice', targetEntity: DocumentAttendu::class)]
private Collection $documentAttendus;

/**
 * @return Collection<int, DocumentAttendu>
*/
public function getDocumentAttendus(): Collection
{
  return $this->documentAttendus;
}

public function addDocumentAttendu(DocumentAttendu $documentAttendu): self
{
  if (!$this->documentAttendus->contains($documentAttendu)) {
    $this->documentAttendus->add($documentAttendu);
    $documentAttendu->setExercice($this);
  }
  return $this;
}

public function removeDocumentAttendu(DocumentAttendu $documentAttendu): self
{
  if ($this->documentAttendus->removeElement($documentAttendu)) {
    if ($documentAttendu->getExercice() === $this) {
      $documentAttendu->setExercice(null);
    }
   }

  return $this;
}

DocumentAttendu

#[ORM\ManyToOne(inversedBy: 'documentAttendus')]
#[ORM\JoinColumn(nullable: false)]
private ?ExerciceComptable $exercice = null;

#[ORM\Column(nullable: true)]
private ?bool $recu = null;

public function getExercice(): ?ExerciceComptable
{
  return $this->exercice;
}

public function setExercice(?ExerciceComptable $exercice): self
{
  $this->exercice = $exercice;
  return $this;
}

public function isRecu(): ?bool
{
  return $this->recu;
}

public function setRecu(?bool $recu): self
{
  $this->recu = $recu;
  return $this;
}

What I tried

$qb = $this->createQueryBuilder( 'ec' );
$qb->join( 'ec.documentAttendus', 'da');
$qb->andWhere('da.recu = true');

This is not working properly. If just one "DocumentAttendu" have "recu" = true, then the query will find it. I need all "DocumentAttendu" to have "recu" = true, not just one out of five for example.

I also tried to use Criteria, but I don't really understand how that works. I tried some line with "having('COUNT')", etc...But I'm not sure I used it correctly.

Important point, I need to be in "ExerciceComptableRepository".

Answer 1

The easiest solution might be a subquery. More specifically, use the Expr class from doctrine. Using a "where not exists (subquery)", should give you the correct results.

You'd get something like:

// This subquery fetches all DocumentAttendu entities
// for the ExerciceComptable where recu is false 
$sub = $this->getEntityManager()->getRepository(DocumentAttendu::class)->createQueryBuilder('da');

$sub->select('da.id');
$sub->where('da.exercice = ec.id');
$sub->andWhere('da.recu IS FALSE');

// We fetch the ExerciceComptable entities, that don't
// have a result from the above sub-query
$qb = $this->createQueryBuilder('ec');

$qb->andWhere($qb->expr()-not(
    $qb->expr()->exists($sub->getDQL()) // This resolves to WHERE NOT EXISTS (...)
))

In short: you're fetching all the ExerciceComptable entities that do not have DocumentAttendu entities with recu = false

Note: if a ExerciceComptable entity doesn't have any documentAttendus, this query will also return that ExerciceComptable entity

Answer 2

My solution is not a full doctrine solution and could make performance issue for larger data, but i believe it could be a great way to deal with very specific case like this.

Lets talk about the correct Sql query before doctrine, it should be something like that :

SELECT ec.id FROM ExerciceComptable ec
   INNER JOIN (SELECT COUNT(*) total, exercice_comptable_id FROM DocumentAttendu) 
   all_documents ON all_documents.exercice_comptable_id = ec.id // COUNT ALL document for each execice
   INNER JOIN (SELECT COUNT(*) total, exercice_comptable_id FROM DocumentAttendu da WHERE da.recu = 1) 
   received_documents ON received_documents.exercice_comptable_id = ec.id // COUNT ALL received document for each execice
WHERE all_documents.total = received_document.total;

Then only the ExerciceComptable with a total documents = received document will be retrieved.
It's important to know that subquery inside select are bad for performance since it doest 1 query for each result (so if you have 100 ExerciceComptable it will do 100 subqueries) where subquery using join only do 1 query for the the whole query. This is why i builded my query like that.

The problem is you wont get entity object with a raw mysql function inside a repositories. So you have two choice.

• Using subqueries inside Doctrine DQL (which is painfull for very complexe case). I advise you to do it only if you have performance issue
• Execute the first query with raw sql -> retrieve only the ids -> call doctrine function findBy(['id' => $arrayOfIds]) -> you have the object you're looking for.

It's a trick, it's true.
But i believe specific usecase with doctrine are often very hard to maintain. Where sql query can be easily tested and changed.
The fact is that only the first will be the one to maintain and the second query will always be very fast since query on id are very fast.

If you want to see a case of DQL with subquery look at : Join subquery with doctrine 2 DBAL

I gave you generic guideline and i hope it helped.

Just never forget : Never Ever do subequeries inside select or where. It has very bad performance since it does one subqueries on server side for each line of result. Use Inner / Left Join to do that