CODEWITHSUNDEEP
mysqlselectdoctrine-orm
watkib
watkib

Reputation: 347

Get last updated records Doctrine Mysql

I need to edit data that exists in a table. Upon editing , i want to keep a copy of the old data. So instead of using an update statement , i am using an insert to insert the new updated data on to the same table. Both sets of data have a certain id that is the same, it helps me keep track of the changes. The table also has an autoincrement field.Something like below: 

> id Data            Data_id
> 1. Data1                01
> 2. Data1_edit1          01
> 3. Data2                02
> 4. Data2_edit1          02

id is autoincrement , Data_id helps keep log of the changes. The table is also timestampable. Such that Data1 will have a lower timestamp than Data1_edit1.

Say I want to get the latest updated records of Data2 i.e id 4 & Data1 i.e id 2 , I am trying to do this using: 

-> select("*") 
-> from("data")
-> groupBy("Data_id")

As is expected this returns Data1 & Data2, how would I be able to return Data1_edit1 and Data2_edit1?

Am using Doctrine 2 and Mysql

Help appreciated.

Thank you.

Upvotes: 0

Views: 1349

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

select * from yourtable
where id in(select max(id) from yourtable group by data_id)

Its working properly. SQL FIDDLE

SQL Fiddle Demo

Upvotes: 2

nab59
nab59

Reputation: 83

You can try this :

select * from data where (data_id, id ) in ( select data_id, max(id) from data group by Data_id );

Upvotes: 1

Related Questions