Thanks a lot!
I hope you do find the time to write a pure DAL query, because i'm so eager
to know how it can be done!
But first i have a workaround that i'll be able to use...
Op dinsdag 4 september 2012 10:06:08 UTC+2 schreef Niphlod het volgende:
>
> 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)
>>>>
>>>>
--
