Resurrecting an old question...
Below is my attempt to perform an effective dated DAL based query, the
results and what i would like to be executed.
Basically given a table with both an effective date (effdt) and an
identifier (i) i want to use a query so i have only one round trip to the
database that returns the currently effective row (the last row for the
given identifier in the past). Such a query is given below, now i want to
create it using the DAL.
def test2():
dbtest = DAL('sqlite:memory:')
dbtest.define_table('a',Field('i','integer'), Field('name','string'),
Field('effdt','datetime'))
dbtest.a.insert(i=1, name='Bob', effdt =
request.now+datetime.timedelta(days=-3))
dbtest.a.insert(i=1, name='Bobbel', effdt =
request.now+datetime.timedelta(days=-2))
dbtest.a.insert(i=2, name='Ed', effdt =
request.now+datetime.timedelta(days=-1))
dbtest.a.insert(i=2, name='Eduard', effdt = request.now)
dbtest.a.insert(i=2, name='Eddy', effdt =
request.now+datetime.timedelta(days=1))
base = BEAUTIFY(dbtest(dbtest.a).select())
outer = dbtest.a.with_alias('out')
ed = dbtest.a.with_alias('ed')
max_from_ed = dbtest(outer.i == ed.i)._select(ed.effdt.max())
result = dbtest(outer.effdt.belongs(max_from_ed) & (outer.i == ed.i) &
(ed.effdt <= request.now)).select(outer.ALL)
a1 = BEAUTIFY(result)
b = dbtest._lastsql
result = dbtest.executesql('select out.* from a out where out.effdt =
(select max(ed.effdt) from a as ed where out.i = ed.i and ed.effdt <=
?)',placeholders=(request.now,))
c = dbtest._lastsql
a2 = BEAUTIFY(result)
return '\n<hr>\n'.join([str(base),str(a1),b,str(a2),c])
The results, in order: the table as in memory, the results using my wrong
dal query, the dal produced sql, my raw sql results and my raw sql
statement:
a.id
a.i
a.name
a.effdt
1
1
Bob
2014-12-05 13:23:52
2
1
Bobbel
2014-12-06 13:23:52
3
2
Ed
2014-12-07 13:23:52
4
2
Eduard
2014-12-08 13:23:52
5
2
Eddy
2014-12-09 13:23:52
------------------------------
out.idout.iout.nameout.effdt52Eddy2014-12-09 13:23:5252Eddy2014-12-09
13:23:52
------------------------------
SELECT out.id, out.i, out.name, out.effdt FROM a AS ed, a AS out WHERE
(((out.effdt IN (SELECT MAX(ed.effdt) FROM a AS ed, a AS out WHERE (out.i =
ed.i))) AND (out.i = ed.i)) AND (ed.effdt <= '2014-12-08 13:23:52'));
------------------------------
21Bobbeldatetime.datetime(2014, 12, 6, 13, 23,
52)42Eduarddatetime.datetime(2014,
12, 8, 13, 23, 52)
------------------------------
select out.* from a out where out.effdt = (select max(ed.effdt) from a as
ed where out.i = ed.i and ed.effdt <= ?)
The last query is what i want to achieve in the DAL. As you can see i have
the issue with the aliases being thrown-in in both the outer and inner
query.
This is the formatted query the DAL produces:
SELECT out.id,
out.i,
out.name,
out.effdt
FROM a AS ed,
a AS OUT
WHERE (((OUT.effdt IN
(SELECT MAX(ed.effdt)
FROM a AS ed,
a AS OUT
WHERE (OUT.i = ed.i)))
AND (OUT.i = ed.i))
AND (ed.effdt <= '2014-12-08 13:23:52'));
and this is what my own query looks like:
SELECT out.*
FROM a OUT
WHERE OUT.effdt =
(SELECT max(ed.effdt)
FROM a AS ed
WHERE OUT.i = ed.i
AND ed.effdt <= ?)
I would really appreciate any help on a DAL only solution that results in
the latter query being executed.
With kind regards,
Remco Boerma
--
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.
