0

I want to find record of particular table whose particular field is modified yesterday and also find current value of field and old value.

For example, I have company table with fields id, name and address. I want to track name field and find current value and old value if it is modified. I will run this at end of the day.

In web2py, If we edit record then two rows are created in archive table, first one contains old data and second one is current record. Can I assume that old row and current record row are always back to back? or There can be row between them? did web2py uses locks while writing into archive table? Please explain how archive entries are created.

Please help!

Gaurav Vichare
  • 1,143
  • 2
  • 11
  • 26

1 Answers1

0

Locks or no locks, I don't think you should use query on just the name field and then go back one row and get the old name. This might create problems for you if the name is not unique (which usually isn't).

A better way to do this is to store the id of the company table in your archive table and use that to query the old name.

So your archive table should now look like this,

| id | name | company_id |

Then using a query on the archive table you can get all rows for a given company_id:

rows = (db.archive.company_id == company_id).select()

And, to get the old name you will have to do something like,

old_name = rows[len(rows) - 1].name

This will return you the last name that you were originally looking for. I hope this helps.

Mujeeb
  • 995
  • 1
  • 8
  • 18
  • I am using archive table generated by web2py not my own archive table. see [Record Versioning](http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=archive#Record-versioning) – Gaurav Vichare Jan 20 '16 at 05:38