选择上一个值学说查询

Question

My table looks something like this

id  |  name   | revision_number | form_key | locked
---------------------------------------------------
1   |  Name1  |        1        |   abcd   |  true
2   |  Name2  |        1        |   efgh   |  false
3   |  Name1  |        2        |   abcd   |  true
4   |  Name2  |        2        |   efgh   |  true
5   |  Name2  |        3        |   efgh   |  true

Now I want to get the forms that were locked last, bases on form_key, so in this case the one with id = 3 and name = Name1 and the one with id = 5 and name = Name2

This is the query I currently have

$revisionQuery =$em->getRepository('AppBundle:Form')->createQueryBuilder('f')
->select('f')

->where('f.lockBit = :locked')

->andWhere('f.revisionNumber = (SELECT MAX(f2.revisionNumber) FROM AppBundle:Form f2 WHERE f.formKey = f2.formKey GROUP BY f2.formKey)')

->setParameters(['locked' => true]);

But this doesn't give me the results I want.

Answer 1

I give to you the SQL for do it. I think you are going to need a Native DQL for Doctrine

SELECT
    f.id,
    f.`name`,
    f.revision_number,
    f.form_key
FROM
    form f
INNER JOIN (
    SELECT
        max(ff.revision_number) AS revision,
        ff.form_key
    FROM
        form ff
    GROUP BY
        ff.form_key
) tt ON f.form_key = tt.form_key
AND f.revision_number = tt.revision

Hope this help you.