Help me to write SQL query in web2py

[Amit]

I have written one sql query:

select device_id, sensor_id, max(created_on) from test.abc_tbl where date_sub(now(), interval interval_time minute) > created_on group by device_id, sensor_id;

How to write it in web2py?

Please help me to do the same.

Thanks.

[Massimo Di Pierro]

You can try this:

from gluon.dal import Expression

t = db.abc_tbl

max_created_on = t.max()

query = Expression(db,"date_sub(now(), interval interval_time minute)")

db(query).select(t.device_id,t.sensor_id,t.max(),group_by=t.device_id|t.sensor_id)

[mcamel]

Hi,

Massimo, i really like your framework. Thank you very much for your dedication.

I'm newbie and don't want to bother but... i think some details are missing in the manual so that we can build this kind of selects by our own, or even understand the query below.

Are there references to 'created_on' missing?. I mean, i expected your 3rd line to be:
    max_created_on = t.max(created_on)

and then use one of the two sides of the expression on the 5th line:
    db(query).select(t.device_id,t.sensor_id,t.max(created_on),...
or
    db(query).select(t.device_id,t.sensor_id,max_created_on,...

also 4th line souldn't be...?:
    query = Expression(db,"date_sub(now(), interval interval_time minute) > created_on")


Hope not to bother.
Many thanks.


BTW, i offer myself to help to improve some parts of the manual from the point of view of someone who is yet learning.

Best regards,
    mcamel.

[Massimo Di Pierro]

My bad. It should be

t.created_on.max()

[Amit]

Thanks a lot Massimo and mcamel, its working as expected :)

Regards,
Amit

--
 
 
 

[mcamel]

Hi Amit,

Could you post the final code that works?.

Thanks.

[Amit]

Hi mcamel,

Please find below the working code:

from gluon.dal import Expression

tbl = db.abc_tbl
max_created_on = tbl.created_on.max()
query = Expression(db,"date_sub(now(), interval interval_time minute) > created_on")    rows=db(query).select(tbl.device_id,tbl.sensor_id,tbl.interval_time,max_created_on,groupby=tbl.device_id|tbl.sensor_id)

Regards,
Amit

--
 
 
 

[mcamel]

Amit,

Does it works if you use tbl.created_on.max() inside the select(), instead of max_created_on?.

I mean, is it necessary to define max_created_on or just convenient for short.

Thanks.

[Amit]

It should work from inside select() also, i used it only for convenient.

Regards,
Amit

--