My bad. Thry this: fields1 = [db.dataset1.date, db.dataset1.param1, db.dateset1.patient_id] fields2 = [db.dataset2.date, db.dataset2.test1, db.dateset2.patient_id] rows = ( db(db.dataset1).select(*fields1) | db(db.dataset2).select(*fields2) ).sort(lambda row: row.date)
Mind that having a column called "date" will result in major headaches in the future. On Sunday, 16 December 2012 03:15:46 UTC-6, Mamisoa Andriantafika wrote: > > Hi again, > > "|" seems not to work because the column numbers is different between the > 2 tables? > > > Le samedi 15 décembre 2012 20:23:44 UTC+1, Massimo Di Pierro a écrit : >> >> If you have lots of records you may be able to do it with a database view >> but that may be db specific. >> >> If you don't have too many records you can do: >> >> rows = ( db(db.dataset1).select() | db(db.dataset2).select() >> ).sort(lambda row: row.date) >> >> >> >> On Saturday, 15 December 2012 08:43:35 UTC-6, Mamisoa Andriantafika wrote: >>> >>> Hi, >>> >>> I have this db model: >>> >>> db.define_table('patients', >>> Field('name', 'string', length=32), >>> Field('firstname', 'string', length=32), >>> Field('dob', 'date'), >>> format='%(name)s') >>> >>> db.define_table('dataset1', >>> Field('date', 'date', length=32), >>> Field('param1', 'string', length=50), >>> Field('param2', 'string', length=50), >>> Field('patient_id', db.patients, writable=False, readable=False)) >>> >>> db.define_table('dataset2', >>> Field('date', 'date', notnull=True), >>> Field('test1', 'text'), >>> Field('patient_id', db.patients, writable=False, readable=True)) >>> >>> I'd like to show in one view, for 1 patient_id, all the corresponding >>> dataset1 and dataset2 ordered by date. >>> >>> What query should I use? Do I have to use an intermediate table >>> 'history' to record each activity in dataset1/2 to get a result? >>> >>> Thanks for help. >>> >> --