Piklu Dey Piklu Dey - 1 month ago 9x
Python Question

Web2py DAL find the record with the latest date

Hi I have a table with the following structure.


Sample Table Structure:

1 | UIN_TX_1 | 1 | txn_summary | 2016-09-02 16:02:42 |
2 | UIN_TX_2 | 1 | txn_summary | 2016-09-02 16:16:56 |
3 | UIN_AD_3 | 2 | some other doc| 2016-09-02 17:15:43 |

I want to fetch the latest modified record UIN for the company whose id is 1 and document_name is "txn_summary".

This is the postgresql query that works:

select distinct on (company_id)
from documents
where comapny_id = 1
and document_name = 'txn_summary'
order by company_id, "modified_on" DESC;

This query fetches me UIN_TX_2 which is correct.

I am using web2py DAL to get this value. After some research I have been successful to do this:

fmax = db.documents.modified_on.max()
query = (db.documents.company_id==1) & (db.documents.document_name=='txn_summary')

rows = db(query).select(fmax)

Now "rows" contains only the value of the modified_on date which has maximum value. I want to fetch the record which has the maximum date inside "rows". Please suggest a way. Help is much appreciated.

And my requirement extends to find each such records for each company_id for each document_name.


Your approach will not return complete row, it will only return last modified_on value.

To fetch last modified record for the company whose id is 1 and document_name "txn_summary", query will be

query = (db.documents.company_id==1) & (db.documents.document_name=='txn_summary')
row = db(query).select(db.documents.ALL, orderby=~db.documents.modified_on, limitby=(0, 1)).first()

orderby=~db.documents.modified_on will return records arranged in descending order of modified_on (last modified record will be first) and first() will select the first record. i.e. complete query will return last modified record having company 1 and document_name = "txn_summary".

There can be other/better way to achieve this. Hope this helps!