Doctrine2 Self Join, How to join on self without relationship column?

Question

Example mysql query:

SELECT
a1.*
FROM
agreement a1
LEFT JOIN agreement a2 on a1.agreementType = a2.agreementType and a2.id > a1.id
WHERE
a2.id is null

The purpose of the query is to get the last agreement of the type returned. There are many types and I want only a listing of each latest agreement for each type. My example query above works as expected, but no go in DQL.

How would I do this in DQL given I do not have a column that refers to itself? Note that "agreementType" is also a foreign key to a different table as well.

Answer 1

Figured it out. Thought I would share.

        SELECT
            a1
        FROM
            My\Model\Agreement a1
            LEFT JOIN My\Model\Agreement a2
                WITH a1.agreementType = a2.agreementType AND a2.id > a1.id
        WHERE
            a2.id IS NULL