Nice! You should sign up on experts4solutions.com. You'd approve you immediately.
Massimo On Thursday, December 20, 2012 4:40:15 PM UTC-6, Mamisoa Andriantafika wrote: > > I finally found the solution using COALESCE to merge and order date > columns: > > exams_mutex = db().select(db.dataset1.date.coalesce(db.dataset2.date), db. > dataset1.ALL, db.dataset2.ALL, left=[db.dataset1.on(db.mutex.i==0),db. > dataset2.on(db.mutex.i==1)], orderby=db.dataset1.date.coalesce(db.dataset2 > .date)) > > Mamisoa > > Le jeudi 20 décembre 2012 16:08:34 UTC+1, Massimo Di Pierro a écrit : >> >> Sorry I do not have a good solution at the moment. Can you open a ticket >> about this so we'll will not forget. For now you may want to consider >> creating a database view and selecting from the view. You would need to >> create a model (readonly) to access the view. >> >> On Wednesday, 19 December 2012 16:19:33 UTC-6, Mamisoa Andriantafika >> wrote: >>> >>> 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. >>>>>>>> >>>>>>> --
