Find all Emails associated with a Case

Is it possible to write DQL query that finds all Email entities assocated with a Case? I know they’re related because I see a table “oro_rel_265353709e0854fe307b0c” whose columns are “email_id” and “caseentity_id”, and because I see the associated emails from the Case view page!

I just don’t know how this relationship is modeled in the ORM.

Topic

[Vova SorokaParticipant]

Hi @ianfp,

The relationship between Case entity and Email entity is unidirectional many-to-many. The owning side is the Email entity.

You can do the following:

PHP

$qb = $em->getRepository('Oro\Bundle\EmailBundle\Entity\Email') ->createQueryBuilder('e'); $this->container->get('oro_activity.manager') ->addFilterByTargetEntity($qb, 'OroCRM\Bundle\CaseBundle\Entity\CaseEntity', $caseId);

1 2 3 4

$qb = $em->getRepository('Oro\Bundle\EmailBundle\Entity\Email')     ->createQueryBuilder('e'); $this->container->get('oro_activity.manager')     ->addFilterByTargetEntity($qb, 'OroCRM\Bundle\CaseBundle\Entity\CaseEntity', $caseId);

The result DQL will be

SELECT e FROM Oro\Bundle\EmailBundle\Entity\Email e WHERE e.id IN( SELECT filterActivityEntity.id FROM Oro\Bundle\EmailBundle\Entity\Email filterActivityEntity INNER JOIN filterActivityEntity.case_entity_81e7ef35 filterTargetEntity WHERE filterTargetEntity.id = :targetEntityId )

1 2 3 4 5 6 7 8

SELECT e FROM Oro\Bundle\EmailBundle\Entity\Email e WHERE e.id IN(     SELECT filterActivityEntity.id     FROM Oro\Bundle\EmailBundle\Entity\Email filterActivityEntity       INNER JOIN filterActivityEntity.case_entity_81e7ef35 filterTargetEntity     WHERE filterTargetEntity.id = :targetEntityId )

where :targetEntityId equals $caseId

[ianfpParticipant]

Worked perfectly! Thank you!

[Author]

Replies