Databases
StackOverflow2013
Postsdbo
POJOIN over four tables in Symfony and Doctrine
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJOIN over four tables in Symfony and Doctrine
    primarykey
    data
    text
    <p>This is my issue what i posted to Symfony forum, but since it didn't get any feedback from there, lets try here :)</p> <p>I have four entities: AdminBundle:Employee, AuthBundle:Login, AdminBundle:Employeephone and AdminBundle:EmployeeEmail.</p> <p>Of course they have relatons to each other:</p> <p><strong>Employee Entity</strong></p> <pre><code>/** * @var Doctrine\Common\Collections\ArrayCollection $login * @ORM\OneToMany(targetEntity="Crm\AuthBundle\Entity\Login", mappedBy="employee_id", cascade={"persist"}) */ protected $login; /** * @var Doctrine\Common\Collections\ArrayCollection $phone * @ORM\OneToMany(targetEntity="Crm\AdminBundle\Entity\EmployeePhone", mappedBy="employee_id", cascade={"persist"}) */ protected $phone; /** * @var Doctrine\Common\Collections\ArrayCollection $email * @ORM\OneToMany(targetEntity="Crm\AdminBundle\Entity\EmployeeEmail", mappedBy="employee_id", cascade={"persist"}) */ protected $email; </code></pre> <p><strong>Login Entity</strong></p> <pre><code>/** * @ORM\ManyToOne(targetEntity="Crm\AdminBundle\Entity\Employee", inversedBy="login", cascade={"persist"}) * @ORM\JoinColumn(name="employee_id", referencedColumnName="id")  */ protected $employee; </code></pre> <p><strong>Phone Entity</strong></p> <pre><code>/** * @ORM\ManyToOne(targetEntity="Crm\AdminBundle\Entity\Employee", inversedBy="login", cascade={"persist"}) * @ORM\JoinColumn(name="employee_id", referencedColumnName="id") */ private $employee; </code></pre> <p><strong>Email Entity</strong></p> <pre><code>/** * @ORM\ManyToOne(targetEntity="Crm\AdminBundle\Entity\Employee", inversedBy="login", cascade={"persist"}) * @ORM\JoinColumn(name="employee_id", referencedColumnName="id") */ private $employee; </code></pre> <p>Now i want to run query what gets list of all users with their primary phone and email (only one is primary), in SQL i would write this query like this:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM `login` LEFT JOIN `employee` on `login`.`employee_id` = `employee`.`id` LEFT JOIN `employeephone` on `employee`.`id` = `employeephone`.`employee_id` LEFT JOIN `employeeemail` on `employee`.`id` = `employeeemail`.`employee_id` WHERE `employeephone`.`is_primary`=1 AND `employeeemail`.`is_primary`=1 </code></pre> <p>But i can't figure out, how it should look like on Doctrine world. I tried to do it like this:</p> <pre><code>class EmployeeRepository extends EntityRepository {     public function getAllEmployees() {         $dql = "SELECT e, l, p, m FROM AuthBundle:Login l                 JOIN l.employee e                 JOIN e.phone p                   JOIN e.email m";         $query = $this-&gt;getEntityManager()-&gt;createQuery($dql);         try {             return $query-&gt;getArrayResult();         } catch (DoctrineORMNoResultException $e) {             return null;         }     } } </code></pre> <p>But it gives me little nice error:</p> <blockquote> <p>Notice: Undefined index: employee_id in C:\wamp\www\Symphony\vendor\doctrine\orm\lib\Doctrine\ORM\Query\SqlWalker.php line 826</p> </blockquote> <p>I've tried several other methods, but all of them lead me to different errors. So how to do it correctly ? After endless hours of googel'ing i havent figured it out yet.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USA.L
    1. USKpihus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJOIN over four tables in Symfony and Doctrine
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJOIN over four tables in Symfony and Doctrine
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload