title: DQL to join on property of a property
body: <p>I am trying to create a DQL query by testing on the property of a property. The objective is to create a DQL query that will indicate in what role a user is fulfilling a job, returning no rows if the user does not have the proper role.</p>
  
  <p>My Entities are Role, Job, and User. User's have roles, Jobs require a role (to fulfill them), and Roles have 'alternates' that link to another role that can fill in for that role.</p>
  
  <p>Stubbed versions of the entites look like:</p>
  
  <pre><code>class User {
      //Annotation not needed for question 
      protected $id;
  
      /**
        * @var SystemBundle\Entity\Role
        *
        * @ORM\ManyToMany(targetEntity="SystemBundle\Entity\Role")
        * @ORM\JoinTable(name="User_User__Role",
        *   joinColumns={
        *     @ORM\JoinColumn(name="User_ID", referencedColumnName="ID")
        *   },
        *   inverseJoinColumns={
        *     @ORM\JoinColumn(name="Role_ID", referencedColumnName="ID")
        *   }
        * )
        */
       protected $roles;
  }
  
  class Job {
      //Annotation not needed for question 
      protected $id;
  
     /**
       * @var SystemBundle\Entity\Role $jobRole
       *
       * @ORM\ManyToOne(targetEntity="Role")
       * @ORM\JoinColumns({
       *   @ORM\JoinColumn(name="Role_ID", referencedColumnName="ID")
       * })
       */
      protected $jobRole;
  }
  
  class Role {
      //Annotation not needed for question 
      protected $id;
  
     /**
       * @var SystemBundle\Entity\Role $jobRole
       *
       * @ORM\OneToOne(targetEntity="Role")
       * @ORM\JoinColumn(name="BackupRole_ID", referencedColumnName="ID")
       */
      protected $backUpRole;
  }
  </code></pre>
  
  <p>The conceptual logic is to join Roles with Jobs, testing if the current Role IS job.JobRole or job.JobRole.backUpRole. I've tried this, and doctrine really doesnt seem to like the job.jobRole.backUpRole and throws this error at the second period:</p>
  
  <pre><code>[Syntax Error] line 0, col 210: Error: Expected =, &lt;, &lt;=, &lt;&gt;, &gt;, &gt;=, !=, got '.' 
  </code></pre>
  
  <p>My DQL attempt looks like this</p>
  
  <pre><code>            SELECT r
              FROM SystemBundle:Role r
              INNER JOIN ProcessBundle:Job j
              WITH j = :j AND j.jobRole = r OR j.jobRole.backUpRole = r
              LEFT JOIN UserBundle:User u
              WITH r MEMBER OF u.roles
              WHERE u = :emp AND u IS NOT NULL 
              ORDER BY r.id
  </code></pre>
  
  <p>I can accomplish this task with pure SQL, as well as just using php to walk the associations, but I am looking to stay true to use DQL (because it's vexing me and I want to know if it can be done).</p>
  
  <p>If it helps, here is my pure SQL:</p>
  
  <pre><code>#get all roles
  select r.*
  from Sys_Role as r
  inner join
  
  #get the role assigned to the job
  (select r.*
  FROM Sys_Role as r
  INNER JOIN Sys_Job as j
  ON j.JobRole_ID = r.ID
  WHERE j.ID = ?) as jr
  
  #join roles based on the job-role's id or backup id
  # this should filter the list of roles down to the role and backup role for the job
  on ar.ID = aar.ID OR  ar.ID = aar.BackUpRole_ID
  
  # and filter by the roles the user is assigned
  INNER JOIN User_User__Role as uur
  ON r.ID = uur.Role_ID 
  WHERE uur.User_ID = ?
  </code></pre>
  
  <p>EDIT: My apologies. While editing the question layout i accidently deleted the DQL. I've added that back the question!</p>
  
  <p>UPDATE:  I am exploring making the $backUpRole a bidirectional self-join and attacking from that direction. Doctrine does not like the naive attempt, so it looks like if this tact is used, fresh DQL is needed.</p>
  
  <p>Changing the DQL to <code>WITH j = :j AND j.jobRole = r OR j.jobRole = r.roleIBackUp</code> yeilds a new error: <code>A single-valued association path expression to an inverse side is not supported in DQL queries. Use an explicit join instead.</code></p>