Web2py DAL find the record with the latest date

Question

Hi I have a table with the following structure.

Table Name: DOCUMENTS

Sample Table Structure:

ID | UIN      | COMPANY_ID | DOCUMENT_NAME | MODIFIED_ON         |
---|----------|------------|---------------|---------------------|
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)
       uin
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.

Answer 1

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!