definitely. I don't have time to test at the moment a "vanilla" DAL
solution, but if you use 2.0.6 you can use
db.executesql("""your query""", fields=[db.person.EmployeeID,
db.person.name])
and get back a "normal" rows object.
Il giorno martedì 4 settembre 2012 09:23:43 UTC+2, Remco Boerma ha scritto:
>
> Indeed. The point is to get the list of employeenames based on the
> effective date (so get the most up to date row not in the future). The
> query i provided works, and there's no way that i know of to do it more
> effectively. Your query would require a second roundtrip to the database
> to get all the names for the found dates and that's what i want to avoid
> because we use this technique also with sets of hundreds of thousands of
> rows.
>
> Anyone else who has a guess how to rewrite this using the DAL? Is there
> any way to alias and subquery? Or can raw sql be added to the where clause?
>
> Thanks
>
> Op donderdag 30 augustus 2012 11:02:02 UTC+2 schreef Niphlod het volgende:
>>
>> I think the point here is retrieve only the record with the same
>> employeeid that has the max date. if so, why can't we rewrite that ugly
>> query as (a much more performant)
>>
>> SELECT
>> EmployeeID,
>> MAX(effdt)
>> FROM person
>> WHERE (effdt < now)
>> GROUP BY EmployeeID
>>
>> ? it's because you need the name ?
>>
>> Il giorno giovedì 30 agosto 2012 08:32:21 UTC+2, Johann Spies ha scritto:
>>>
>>> Hallo Remco,
>>>
>>>
>>> Try this:
>>> import datetime
>>> now = datetime.datetime.now()
>>> query = db.person.effdt > now
>>> data = SQLFORM.grid(query)
>>>
>>> Regards
>>> Johann
>>>
>>> --
>>> Because experiencing your loyal love is better than life itself,
>>> my lips will praise you. (Psalm 63:3)
>>>
>>>
--
