How did I not know about this gem earlier?
I've been resorting to executesql() for my heavy joins-within-joins queries
because, well, I thought I had to. The select join syntax doesn't do
nested joins directly and I'd never heard of nested_select.
Wow, what a great tool! I just make my sub-join its own select, make an
alias for it (required to use it with "on") and then put it in another
query. It is a thing of beauty. (Well, maybe I have low standards of
beauty!)
This result is the equivalent of a very complex executesql() query:
def gettimes(self, mid, eid, stage):
assert mid and eid and stage
db = current.db
dbt,dbc,dbs,dbei,dbts,dbti =
db.times,db.club,db.swimmer,db.event_index,db.time_std,db.time_std_info
cols = [dbt[c] for c in
['ord','rank','evt','heat','lane','age','stime','stage','id']]
cols += [dbc[c] for c in ['code','name','id']]
cols += [dbei[c] for c in
['course','sex','distance','stroke','lower_age','upper_age']]
cols += [dbs[c] for c in ['id','first','last','mi','preferred']]
subq = db(dbts.id>0).nested_select(dbts.ALL,
join=dbti.on((dbti.id==dbts.id_info)&(dbti.id_lsc=='51')&(dbti.code=='Default')))
dbtt = subq.with_alias('dbtt')
cols += [dbtt[c] for c in ['name','next','time']]
q = (dbt.id_meet==mid)&(dbt.id_event==eid)&(dbt.stage==stage)
rows = db(q).select(*cols,cacheable=True,processor=self.as_list,
join=[dbei.on(dbei.id==dbt.id_event),
dbc.on(dbc.id==dbt.id_club),
dbs.on(dbs.id==dbt.id_swim)],
left=dbtt.on
((dbtt.event_code==dbei.code2)&(dbt.stime>0)
&(dbtt.lower_age<=dbt.age)&(dbtt.upper_age>=dbt.age)
&(dbtt.next<dbt.stime)&(dbtt.time>=dbt.stime)))
return rows
To save creation of Rows I used the "processor" keyword and made a little
processor that basically returns a list of nested dicts, just as the normal
"as_list" would to to a set of Rows. But it does it without creating the
Rows first, saving time and memory.
@staticmethod
def as_list(rows, fields, colnames, blob_decode=True, cacheable =
False):
from collections import defaultdict
rtn = list()
cols = [(f.tablename,f.name) for f in fields]
for row in rows:
d = defaultdict(dict)
for r,v in zip(cols,row):
d[c[0]][c[1]] = r
rtn.append(d)
return rtn
I'm pleased to get really good performance and still keep the power and
generality of DAL. This feature might deserve additional explanation in
the book.
-- Joe
If you want to see the executesql version, hold your nose and peek below.
I'm sure it is ghastly and could be done much better. There are two
variants I was playing with, one that uses Rows and as_list and another
which makes the list of nested dicts directly.
def gettimes2(self, mid, eid, stage):
db = current.db
dbt, dbc, dbs, dbei, dbts, dbti = db.times, db.club, db.swimmer,
db.event_index, db.time_std, db.time_std_info
assert mid and eid and stage
table_alias =
{'times':'dbt','club':'dbc','swimmer':'dbs','event_index':'dbei','time_std':'dbtt'}
colnames =
['times.ord','times.rank','times.evt','times.heat','times.lane','times.age','times.stime','times.stage','times.id',
'club.code','club.name','club.id',
'event_index.course','event_index.sex','event_index.distance','event_index.stroke',
'event_index.lower_age','event_index.upper_age',
'swimmer.id','swimmer.first','swimmer.last','swimmer.mi','swimmer.preferred',
'time_std.name','time_std.next','time_std.time']
split_colnames = [fld.split('.') for fld in colnames]
rnames=['.'.join([table_alias[t],db[t][f]._rname]) for t,f in
split_colnames]
sqlvars = dict(eid=eid, mid=mid, stage=stage,
fields=','.join(rnames))
stmt = """
SELECT %(fields)s FROM sstimes.times dbt
JOIN ssdata.event_index dbei ON dbei.id=dbt.id_event
JOIN ssdata.club dbc ON dbc.id=dbt.id_club
JOIN sstimes.swimmer dbs ON dbs.id=dbt.id_swim
LEFT JOIN (
SELECT dbts.*,dbti.id_lsc FROM ssdata.time_std dbts
JOIN ssdata.time_std_info dbti
ON dbti.id=dbts.id_info AND dbti.id_lsc=51 AND
dbti.code='Default'
) dbtt ON (
dbtt.event_code=dbei.code2 AND dbt.stime>0
AND dbtt.lower_age<=dbt.age AND dbtt.upper_age>=dbt.age
AND dbtt.next<dbt.stime AND dbtt.time>=dbt.stime
) WHERE dbt.id_meet=%(mid)s AND dbt.id_event=%(eid)s AND
dbt.stage='%(stage)s'
"""
if False:
fields = [db[t][f] for t,f in split_colnames]
rtn = db.executesql(stmt%sqlvars, fields=fields).as_list()
else:
rows = db.executesql(stmt%sqlvars, colnames=colnames,
as_dict=True)
# vvv turn [name1.name2] into [name1][name2], not needed
fields=columns
from collections import defaultdict
rtn = []
for r in rows:
d = defaultdict(dict)
for k,v in r.items():
t,f = k.split('.')
d[t][f] = v
rtn.append(d)
# ^^^ end of conversion
return rtn
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
