Best results to UNION I could have was using the mutex table trick: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ with this syntax:
exams_mutex = db().select(db.dataset1.ALL, db.dataset2.ALL, left=[db.dataset1.on(db.mutex.i==0),db.dataset2.on(db.mutex.i==1)]) Two problems left: 1- how to merge the 2 date columns 2- how to order then by date SQL answer is there: http://stackoverflow.com/questions/8245630/mysql-combine-two-date-fields-and-then-order-by SELECT [some info], GREATEST( ticket_date, ticket_history_date ) as latest_date FROM [tables and join] ORDER BY latest_date Is is possible using to process it using DAL? Le dimanche 16 décembre 2012 17:40:22 UTC+1, Mamisoa Andriantafika a écrit : > > Sorry I still get: "Cannot | incompatible Rows objects". > > I'll change the field name "date" you are very right. > > Le dimanche 16 décembre 2012 15:49:57 UTC+1, Massimo Di Pierro a écrit : >> >> 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. >>>>> >>>> --
