If you only need one date, why not display just one date? On Friday, December 21, 2012 2:01:59 PM UTC-7, Mamisoa Andriantafika wrote: > > I have pb to show the coalesce date in view. > I tried; > > exams_mutex['COALESCE(dataset1.date,dataset1.date)'] > > but it is not working? > > Le jeudi 20 décembre 2012 23:40:15 UTC+1, Mamisoa Andriantafika a écrit : >> >> I finally found the solution using COALESCE to merge and order date >> columns: >> >> exams_mutex = db().select(db.dataset1.date.coalesce(db.dataset1.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. >>>>>>>>> >>>>>>>>
--
