How do I apply a user defined function on a query in web2py DAL?

I am new to web2py frame work, I am building an application where by I need to determine records from the database which are a given period(say months) ago basing on the dates I recorded them with (Assignment_date), for example, listing cases which are 1 month, 2 months or 3 months old. so here is how I had done it but its not working in the model I define my tables and I have these two functions one converts a parsed date (date from the database) and the other does date difference basing on current system time and returns an epoch difference of the two dates.

stage =("Appeal","Investigation","Pre-Trial","Trial","Second Appeal")
status =("Draft","Open")
db.define_table('lfm_case',
Field('title',requires=IS_NOT_EMPTY()),
Field('Assignment_date','date',requires=IS_DATE(format=T('%Y-%m-%d'))),
Field('problem','text'),
Field('reference_number',requires=IS_NOT_EMPTY()),
Field('institution'),
Field('notes','text',requires=IS_NOT_EMPTY()),
Field('stage',requires=(IS_IN_SET(stage,multiple=False),IS_NOT_EMPTY()),default='Investigation'),
Field('status',requires=(IS_IN_SET(status,multiple=False),IS_NOT_EMPTY()),default='Open'),
Field('case_scope','integer',default=1,readable=False,writable=False),
auth.signature)

import datetime
def to_epoch(a):
date_obj = datetime.datetime.strptime(str(a), "%Y-%m-%d")
epoch = int(date_obj.strftime('%s'))
return epoch

def date_diff(b):
current_date = datetime.datetime.now()
current_epoch = int(current_date.strftime('%s'))
diff = (current_epoch - to_epoch(b))
return diff


in the controller I have a function that tries to run a query to fetch all date whose date difference is <= the 26297435(that's a month) here is the function

def list_by_date():
rows = db(date_diff(db.lfm_case.Assignment_date) <= 26297435).select(orderby=db.lfm_case.title)
return locals()


Below is my view

{{extend 'layout.html'}}
<table>
<tr>
<th>Date</th>
<th>Title</th>
<th>Status</th>
</tr>
{{for row in rows:}}
<tr>
<td>{{=row.Assignment_date}}</td>
<td>{{=row.title}}</td>
<td>{{=row.status}}</td>
</tr>
{{pass}}
</table>


1 Answer
1

db(date_diff(db.lfm_case.Assignment_date) <= 26297435).select(orderby=db.lfm_case.title)


First, db.lfm_case.Assignement_date is a DAL Field object, not a datetime object or string as required by date_diff. Second, the query that goes inside db() will ultimately be converted to SQL to be executed by the database engine, so it cannot involve the execution of arbitrary Python code -- it must be limited to what can be expressed via SQL.

db.lfm_case.Assignement_date

Field

date_diff

db()

Instead, because the database is storing date values, you should supply an actual date for comparison in the query (i.e., instead of calculating a timestamp, calculate the associated date). For example:

thirty_days_ago = datetime.datetime.now() - datetime.timedelta(days=30)
db(db.lfm_case.Assignment_date <= thirty_days_ago).select(...)


Alternatively, different databases provide their own functions for doing calculations with dates, so you can instead pass your own custom raw SQL as a DAL query:

db(custom_sql_string).select(db.lfm_case.ALL, ...)


Note, if the query passed to db() is just a raw SQL string not joined with any DAL Query objects via & or |, then the .select() must specify either some specific fields to select or db.lfm_case.ALL (which represents all fields) so that the DAL knows which table is involved in the query (it cannot determine that from a raw SQL string).

db()

Query

&

|

.select()

db.lfm_case.ALL

Thank you very much, it works fine and i'll go with the first option that you suggested
– E.Keeya
2 days ago

Great. Feel free to accept the answer so others know it works as well.
– Anthony
2 days ago

thirty_days_ago = datetime.datetime.now() - datetime.timedelta(days=30) db(db.lfm_case.Assignment_date <= thirty_days_ago).select(...)
– E.Keeya
2 days ago

I just had to change the <= to >= because you realize all the dates of say year 2015 are less that (now()-30 days) so changing it to >= gives you only dates that are greater than or equal to (now() - 30 days) which i wanted then i can simply substitute 30 with a variable and parse any value to accupy that variable hence i can get dates say (IN 1 month), (IN 2 months) etc thank you so much so now how do i accept the your answer??
– E.Keeya
2 days ago

Next to the answer there are up and down arrows to vote, and a check mark you can click to indicate the answer is correct.
– Anthony
2 days ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.