Thanks a lot, i learned something new today :) Obvious solution, once you think of it, and much quicker than building loops.
Best regards, Jaap On Monday, December 29, 2014 10:51:01 AM UTC+1, Massimo Di Pierro wrote: > > db.employee_contract.contract_id == db.contract.id) > & (db.employee_contract.employee_id == db.employee.id > > I think you should do it: > > contracts = ((db.employee_contract.contract_id == db.contract.id)& > (db.employee_contract.employee_id == db.employee.id)) > > rows = db(contracts).select(db.employee.ALL, db.contract.ALL, orderby= > db.employee.id|~db.contract.start_date, distinct=db.employee.id) > > On Sunday, 28 December 2014 15:57:10 UTC-6, JaapP wrote: >> >> Hi, >> >> Any hints about how to tackle the following problem greatly appreciated: >> >> given three tables: >> >> *employee*: >> id >> name >> ... >> >> *contract*: >> id >> start_date >> fte100 >> ... >> >> *employee_contract:* >> id >> employee_id >> contract_id >> >> An employee can have one or more contracs; they are linked through the >> employee_contract table. >> >> I would like to find a query that returns the contract details for the >> latest contract for an employee; >> >> i've solved a little step of my puzzle and can retrieve the latest >> contract by doing something like this: >> >> employee_id = 10 >> >> contr = db( (db.employee.id == employee_id) >> & (db.employee_contract.contract_id == db.contract.id) >> & (db.employee_contract.employee_id == db.employee.id) >> ).select(db.employee.ALL, db.contract.start_date.max()) >> >> latest_start_date = contr.contract.start_date.max() >> >> >> but i need also the corresponding contract.fte100 field. >> >> Can this be done by using the DAL, or should i construct a loop to find >> the answer? >> >> Best regards, >> >> Jaap >> > -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
