Given a DB table (called db.t_schedule, with the field names/types and
corresponding values show below), is there a way to generate an
SQLFORM.grid based on the db.t_schedule table such that:
- There is no grid row which has the same 'f_uuid' as any other grid row
- For each grid row, the earliest ‘start_time’ for a particular 'f_uuid'
is displayed
- The ‘user_id’ and ‘user_id_2’ values corresponding to the
db.t_schedule row with the earliest ‘start_time’ for a given 'f_uuid' are
displayed
- The grid is sorted by the ‘f_start’ field
The db.t_schedule DB data:
id
f_uuid
(string)
f_start
(date)
user_id
(reference db.auth_user.id)
user_id_2
(reference db.auth_user.id)
1
123
2/24/16
1
4
2
123
1/24/16
2
5
3
123
1/15/16
3
6
4
234
2/23/16
4
4
5
234
2/27/16
5
2
6
234
1/2/16
6
8
7
345
2/25/16
7
6
8
345
2/26/16
8
5
The desired SQLFORM.grid resembles the following:
f_uui
(string)
f_start
(date)
user_id
(reference db.auth_user.id)
user_id_2
(reference db.auth_user.id)
123
1/15/16
3
6
234
1/2/16
6
8
345
2/25/16
7
6
I guess one way to do it would be something like the following, but this
doesn’t seem to be the most efficient or practical way of going about it,
especially if dealing with a large number of rows:
dict__ids = {} # key: uuid, val = schedule ID
fields__select = [db.t_schedule.f_uuid,db.t_schedule.id,db.t_schedule.
f_start]
for row__appointment in db(query__select).select(*fields__select, orderby =~
db.t_schedule.f_start):
if not row__appointment['id'] in dict__ids.keys():
dict__ids[row__appointment['f_uuid']] = row__appointment['id']
query = db.t_schedule.id.belongs(dict__ids.values())
grid = SQLFORM.grid(query, ...)
I’ve been tinkering with a variety of query variants, and combinations of
groupby/orderby, but I’m not sure about the most efficient way to generate
a grid which meets the above conditions. Any help would be much
appreciated.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
