doctrinedql

DQL join two joined tables from different tables


I know the title isn't very clear, but I will try to better explain my problem here. I have 3 Doctrine entities : A, B and C

class A {        class B {            class C {
  $id;             $id;                 $id;
  ManyToMany       ManyToMany         }
  $C;              $C; 
}                }

I'm trying to know if an object A and an object B have at least one same C.

The many to many relations gives me table like :

table AC {          table BC {
   A_id;               B_id;
   C_id;               C_id;
}                    }

I know that I can't use these tables in DQL but what I want to do can be done in SQL. It would give :

SELECT COUNT(A.id) FROM AC INNER JOIN BC 
ON AC.C_id = BC.C_id
WHERE BC.B_id=1217 AND AC.A_id=185

Solution

  • You will need to make the many to many association bidirectional, so entities will look like this:

    <?php
    
    namespace App\Model;
    
    use Doctrine\Common\Collections\ArrayCollection;
    use Doctrine\ORM\Mapping as ORM;
    
    /**
     * @ORM\Entity
     */
    class A
    {
        /**
         * @ORM\Id
         * @ORM\Column(type="integer")
         * @ORM\GeneratedValue
         * @var integer
         */
        private $id;
    
        /**
         * @var ArrayCollection|C[]
         * @ORM\ManyToMany(targetEntity="C", inversedBy="as")
         */
        private $cs;
    }
    
    /**
     * @ORM\Entity
     */
    class B
    {
        /**
         * @ORM\Id
         * @ORM\Column(type="integer")
         * @ORM\GeneratedValue
         * @var integer
         */
        private $id;
    
        /**
         * @var ArrayCollection|C[]
         * @ORM\ManyToMany(targetEntity="C", inversedBy="bs")
         */
        private $cs;
    }
    
    /**
     * @ORM\Entity
     */
    class C
    {
        /**
         * @ORM\Id
         * @ORM\Column(type="integer")
         * @ORM\GeneratedValue
         * @var integer
         */
        private $id;
    
        /**
         * @var ArrayCollection|A[]
         * @ORM\ManyToMany(targetEntity="A", mappedBy="cs")
         */
        private $as;
    
        /**
         * @var ArrayCollection|A[]
         * @ORM\ManyToMany(targetEntity="B", mappedBy="cs")
         */
        private $bs;
    
    }
    

    And then you can query the C class with conditional join on A and B entities, by this DQL query:

        $query = $this->entityManager->createQuery("SELECT count(c.id) FROM C::class c INNER JOIN c.as a WITH a.id = :a_id INNER JOIN c.bs b WITH b.id = :b_id")
            ->setParameter('a_id', 185)
            ->setParameter('b_id', 1217);
        $result = $query->getSingleScalarResult();