0

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.

Jaimy
  • 517
  • 4
  • 20
  • what result you want and what result are you getting now? – Juan Carlos Oropeza Mar 04 '16 at 15:02
  • @eggyal an auto_increment column can also be used to determine the order and this is described in the OP – Shadow Mar 04 '16 at 15:11
  • Look here: http://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2, there is your solution how to do subselect in where clausule in doctrine. Of course you need to adapt the solution to your case. – Miro Mar 04 '16 at 15:13
  • The idea is that, in this example, the records with id 1 and 3 are almost identical, but with some small differences. Same goes for records with id 2,4 and 5. The 'identical' records all have the same form_key. What I want to is to get the record that has been locked last. So from the ones with id 2,4 and 5, 4 and 5 are locked and I want to get the one with id 5. At the moment I get the first one that appears in the table, so in this example the one with id 4. – Jaimy Mar 04 '16 at 15:16

1 Answers1

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.

abdiel
  • 2,078
  • 16
  • 24