title: join tables in doctrine2
body: <p>I am trying join the tables, in Doctrine2 Query Builder. This was my query to join the two tables and get the result.</p>
  
  <p>Initially i have two tables to be joined:</p>
  
  <p>--> employees
  --> org_employees</p>
  
  <p>Other tables:</p>
  
  <p>--> users </p>
  
  <p>Here in my Doctrine2 Query builder code, i have joined 2 tables to fetch the results of  particular Organization employees by passing the Organization id.  </p>
  
  <pre><code>SELECT 
  * 
  FROM org_branch_employees oe
  LEFT JOIN employees e ON oe.employee_id  = e.id
  WHERE 
  oe.org_id = 1;
  </code></pre>
  
  <p>By using the Query Builder the above sql code has been changed like below.</p>
  
  <pre><code>$qb = $this-&gt;entityManager-&gt;createQueryBuilder();
          $qb-&gt;select('oe', 'e', 'o')
              -&gt;from('Employee\Entity\OrgEmployee', 'oe')
                  -&gt;leftJoin('oe.organization', 'o')
                  -&gt;leftJoin('oe.employee', 'e')
                  -&gt;where('oe.organization = :organizationId')
                  -&gt;setParameter('organizationId', $orgId);
          $query = $qb-&gt;getQuery();
          $orgEmployees = $query-&gt;getResult();
          return $orgEmployees;
  </code></pre>
  
  <p>This is my Employee Entity:</p>
  
  <pre><code>&lt;?php
  
  namespace Employee\Entity;
  
  use Doctrine\Common\Collections\ArrayCollection;
  use Doctrine\ORM\Mapping as ORM;
  use Library\Entity\BaseEntity;
  use Users\Entity\User;
  use Organization\Entity\Organization;
  
  //use Organization\Entity\OrgEmployee;
  
  /**
   * Description of Employee
   *
   * @author Macwin
   */
  
  /**
   * @ORM\Entity
   * @ORM\Table(name="employees")
   */
  class Employee extends BaseEntity {
  
      /**
       * @ORM\OneToOne(
       *     targetEntity="Users\Entity\User"
       * )
       * @ORM\JoinColumn(
       *     name="user_id",
       *     referencedColumnName="id",
       *     nullable=false
       * )
       */
      private $user;
  
      /**
       * @ORM\Column(name="employee_code", type="string", nullable=true)
       * @var string
       */
      protected $empCode;
  
      /**
       * @ORM\OneToOne(
       *     targetEntity="Organization\Entity\Organization"
       * )
       * @ORM\JoinColumn(
       *     name="org_id",
       *     referencedColumnName="id",
       *     nullable=false
       * )
       */
      private $organization;
  
      /**
       * @ORM\OneToMany(targetEntity="Employee\Entity\OrgEmployee", mappedBy="employee")
       */
      protected $orgEmployee;
  
      /**
       * @ORM\OneToMany(targetEntity="Employee\Entity\OrgEmployeeDesignation", mappedBy="employee")
       */
      protected $orgEmployeeDesignation;
  
      public function __construct() {
          $this-&gt;organizations = new \Doctrine\Common\Collections\ArrayCollection();
          parent::__construct();
      }
  
      public function getOrganizations() {
          return $this-&gt;organizations;
      }
  
      public function addOrganization(Organization $organization = null) {
          $this-&gt;organizations-&gt;add($organization);
      }
  
      public function setUser(User $user = null) {
          $this-&gt;user = $user;
      }
  
      public function getUser() {
          return $this-&gt;user;
      }
  
      public function getEmpCode() {
          return $this-&gt;empCode;
      }
  
      public function setEmpCode($empCode) {
          $this-&gt;empCode = $empCode;
      }
  
      public function setOrganization(Organization $organization = null) {
          $this-&gt;organization = $organization;
      }
  
      public function getOrganization() {
          return $this-&gt;organization;
      }
  
      function getOrgEmployeeDesignation() {
          return $this-&gt;orgEmployeeDesignation;
      }
  
      function setOrgEmployeeDesignation($orgEmployeeDesignation) {
          $this-&gt;orgEmployeeDesignation = $orgEmployeeDesignation;
      }
  
       public function getOrgEmployee() {
          return $this-&gt;orgEmployee;
      }
  
      public function __toString() {
          return __CLASS__ . ": [id: {$this-&gt;id}, name: {$this-&gt;name}]";
      }
  }
  </code></pre>
  
  <p>Here is my OrgEmployee Entity Which maps the Organization table and Enity table, to get the Organization details and Employee Details.</p>
  
  <pre><code>&lt;?php
  
  namespace Employee\Entity;
  use Doctrine\ORM\Mapping as ORM;
  use Library\Entity\BaseEntity;
  use Employee\Entity\Employee;
  use Organization\Entity\Organization;
  
  /**
   * Description of Org Employees
   *
   * @author Macwin
   */
  
  /**
   * @ORM\Entity
   * @ORM\Table(name="org_branch_employees")
   */
  
  class OrgEmployee extends BaseEntity{
  
      /**
       * @ORM\ManyToOne(targetEntity="Employee\Entity\Employee", inversedBy="orgEmployee")
       * @ORM\JoinColumn(name="employee_id",referencedColumnName="id",nullable=false)
       */
      protected $employee;
  
      /**
       * @ORM\ManyToOne(targetEntity="Organization\Entity\Organization", inversedBy="orgEmployee")
       * @ORM\JoinColumn(name="org_branch_id", referencedColumnName="id", nullable=false)
       */
      protected $organization;
  
      public function setEmployee(Employee $employee = null)
      {
          $this-&gt;employee = $employee;
  
          return $this;
      }
  
      public function getEmployee()
      {
          return $this-&gt;employee;
      } 
  
      public function setOrganization(Organization $organization = null)
      {
          $this-&gt;organization = $organization;
  
          return $this;
      }
  
      public function getOrganization()
      {
          return $this-&gt;organization;
      }    
  
  
  }
  </code></pre>
  
  <p>Here is how i am getting the Organization details and Employee Details:</p>
  
  <pre><code>'employeeCode' =&gt; $orgEmp-&gt;getEmployee()-&gt;getEmpCode(),
  
  
   userFirstName =  $orgEmp-&gt;getEmployee()-&gt;getUser()-&gt;getFirstName(),
  </code></pre>
  
  <p>Being, employees table has mapping of users table, i can fetch the users information,</p>
  
  <p>so far so good, but while i am trying to join more tables here, i couldnt bring the exact result.</p>
  
  <p>But when i need to make the filter in the above functionality, i am not sure, how can bring the exact result.</p>
  
  <p>Like filter by employee_code, user first name. </p>
  
  <p>Can anyone guide me to bring the result. I am working on the REST API side to give the result to the client. Pagination was also there in the functionality.</p>
  
  <p>I am trying the following if i am rightly said:</p>
  
  <pre><code>SELECT 
  * 
  FROM org_branch_employees oe
  LEFT JOIN employees e ON oe.employee_id  = e.id
  LEFT JOIN users u ON e.user_id = u.id 
  WHERE 
  oe.org_id = 1 AND 
  u.user_first_name = "John" and 
  e.employee_code = "EMP777"
  </code></pre>