Can Doctrine Join itself

Question

As described in a related question, I have a ViewVersion entity that contains multiple versions for a given View. If I want to fetch a list of the "best" viewVersions based on which is the last modified, you can not do a GROUP BY viewId because you can't control the order these return (e.g. timeMod DESC). So you after days of searching I believe the best way to do this is through a simple INNER JOIN on itself that matches the highest timeMod.

This works really well in MySQL, but how in the world can you do this in Doctrine?

Native MySQL Query

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

Question

How can you form a Doctrine query to join an entity TO ITSELF, using QueryBuilder or DQL (or if absolutely necessary Native).

Everytime I try, the INNER JOIN complains about the SELECT statement like so:

QueryException: [Semantical Error] line 0, col 72 near '(SELECT': Error: Class '(' is not defined

NOTE: this is different than my original question, in that this is focused specifically on joining an entity to itself.

Answer 1

To answer the specifically stated question, you can easily join any entity to itself (even if it does not have a self-referential association), simply by joining to the entity shortcut name (instead of the associated field name like normal).

$viewVersionRepo = $em->getRepository('GutensiteCmsBundle:View\ViewVersion');   
$queryBuilder    = $viewVersionRepo->createQueryBuilder('e')   

    // JOIN ENTITY TO ITSELF - VIA BUNDLE SHORTCUT NAME
    ->join('GutensiteCmsBundle:View\ViewVersion', 'version', 'WITH', 'e.viewId = version.viewId')

    // JOIN OTHER ASSOCIATED ENTITIES - VIA FIELD NAME
    ->join('e.view', 'view')
    ->addSelect('view')
    ->join('view.contentType', 'contentType')
    ->addSelect('contentType')

    // PERFORM RANDOM FILTERS
    ->andWhere('e.siteId = :siteId')->setParameter('siteId', 1)
    ->addOrderBy('e.title', 'ASC');

$query      = $queryBuilder->getQuery();
$results    = $query->getResult();

This however, is NOT accomplishing the same thing as the query in the question. It is NOT joining a temporary table from a select statement, which is what is needed in this case (and the other question referenced). There is no MAX column join here, because you can't GROUP a join. You can GROUP the main query, by a column from the JOINED table, but that doesn't let you perform a MAX on the ON/WITH portion of the JOIN.

But hopefully this answer is at least helpful to anyone else that wanted to know how to join an entity to itself or any random entity.