CODEWITHSUNDEEP
phpdoctrine-ormorm
mcek
mcek

Reputation: 490

Doctrine 2 difference with tables by one to one same relation

I have 2 entities with same one to one relation:

class FirstEntity {

...

 /**
  * @var \App\Entity\ThirdEntity
  *
  * @ORM\OneToOne(targetEntity="App\Entity\ThirdEntity")
  */
 protected $thirdEntity;



class SecondEntity {

...

 /**
  * @var \App\Entity\ThirdEntity
  *
  * @ORM\OneToOne(targetEntity="App\Entity\ThirdEntity")
  */
 protected $thirdEntity;

$thirdEntity field cant be null. Is there any way to return result by query builder or native query like array_diff() opertion:

$firsts = array_map(function (FirstEntity $firstEntity) {
return $firstEntity->getThirdEntity()->getId();
}, $this->em->getRepository(FirstEntity::class)->findAll());

$seconds = array_map(function (SecondEntity $secondEntity) {
return $secondEntity->getThirdEntity()->getId();
}, $this->em->getRepository(SecondEntity::class)->findAll());

return array_diff($firsts, $seconds);

So what I want is a list of all ThirdEntity that is linked to a FirstEntity but not to a SecondEntity.

Upvotes: 0

Views: 55

Answers (1)

Jory Geerts
Jory Geerts

Reputation: 1977

The most performant way to do this, is using a native query or the query builder (or multiple native queries/query buidlers). This way, the database will do all the filtering for you, meaning Doctrine doesn't have to do to much work.

The query builder is the easiest way, but does require you to also map from ThirdEntity to FirstEntity and SecondEntity - the following code assumes you have that mapping in place. 

$qb = $em->createQueryBuilder();
$result = $qb->select('t')->from('ThirdEntity', 't')
    ->innerJoin('t.firstEntity', 'f')
    ->leftJoin('t.secondEntity', 's')
    ->where($qb->expr()->isNull('s.id'));
    ->getQuery()->getResult();

What happens here, is that we ask the database to take all ThirdEntity records, inner join them with all FirstEntity records (so a ThirdEntity without FirstEntity won't be included). Then we left join that with SecondEntity, which means that if the ThirdEntity is also linked to a SecondEntity its data becomes available for filtering, otherwise we get NULL for all the SecondEntity columns. The last step is to select only those records where there is no SecondEntity (meaning its ID is null).

Depening on the exact dataset, this "one query" approach may not give you the best performance. It could be faster to first select the ID's of all ThirdEntity items that have a relation to a FirstEntity and then (in a separate query, where you use that ID list as parameter) filter out those items that also have a relation to a SecondEntity. It might also be faster to do those two things the other way around. I'm not expecting that though, so we'll leave that for another question (or another answer to this question).

Upvotes: 1

Related Questions