title: Can Doctrine Join itself
body: <p>As <a href="https://stackoverflow.com/questions/27007090/inner-join-results-from-select-statement-using-doctrine-querybuilder">described in a related question</a>, I have a <code>ViewVersion</code> entity that contains multiple versions for a given <code>View</code>. If I want to fetch a list of the "best" viewVersions based on which is the last modified, you can <strong>not</strong> do a <code>GROUP BY viewId</code> because you can't control the order these return (e.g. timeMod DESC). So you after days of searching I <em>believe</em> the best way to do this is through a simple INNER JOIN on itself that matches the highest timeMod. </p>
  
  <p>This works really well in MySQL, but how in the world can you do this in Doctrine?</p>
  
  <h2>Native MySQL Query</h2>
  
  <pre><code>SELECT view_version.* 
  FROM view_version
  #inner join only returns where the result sets overlap, i.e. the one record with highest timeMod
  INNER JOIN (
      SELECT MAX(timeMod) maxTimeMod, viewId
      FROM view_version
      GROUP BY viewId
  ) version ON version.viewId = view_version.viewId AND view_version.timeMod = version.maxTimeMod
  #join other tables for filter, etc
  INNER JOIN view ON view.id = view_version.viewId
  INNER JOIN content_type ON content_type.id = view.contentTypeId
  WHERE view_version.siteId=1
  AND view.contentTypeId IN (2)
  ORDER BY view_version.title ASC
  </code></pre>
  
  <h2>Question</h2>
  
  <p>How can you form a Doctrine query to join an entity TO ITSELF, using QueryBuilder or DQL (or if absolutely necessary Native).</p>
  
  <p>Everytime I try, the <code>INNER JOIN</code> complains about the SELECT statement like so:</p>
  
  <pre><code>QueryException: [Semantical Error] line 0, col 72 near '(SELECT': Error: Class '(' is not defined
  </code></pre>
  
  <p><strong><em>NOTE</strong>: this is different than my original question, in that this is focused specifically on joining an entity to itself.</em></p>