[web2py] DAL select queries abstraction is getting fiddly... your advice?

[Alec Taylor]

Alright, I think I've made a pretty tiny test-case.

So basically I want to grab the group and all its events on request.

So I give the request check on `group_name`, followed by an outer left
join using the `group_id` from the `event` table on the
`group_of_events`:

group = db(db.group_of_events.group_name ==
request.args(0)).select(db.group_of_events.ALL, db.event.ALL,
left=db.event.on(db.event.group_id == db.group_of_events.id),
orderby=db.group_of_events.group_name) or redirect(URL('', 'groups'))

Unfortunately this didn't work, giving me an error:
<type 'exceptions.KeyError'> 'group_name'

So I thought that maybe I'd need another select to find the
`group_of_events.id` from the `group_of_events.group_name`, so I ran:

group = db(db.group_of_events.group_name ==
request.args(0)).select(db.group_of_events.ALL, db.event.ALL,
left=db.event.on(db.event.group_id ==
db(db.group_of_events.id).select(db.group_of_events.id)),
orderby=db.group_of_events.group_name) or redirect(URL('', 'groups'))

All in all, it seems to be getting quite messy, and I'm also not sure
why this didn't work.

Also, I can only see this getting messier, because I'll want to link
to a separate event page from user selecting an event.

So advice is required, if you please.

Thanks for all suggestions,

Alec Taylor

PS: Here's the little test-case

# models\social.py

db.define_table(
    'group_of_events',
    Field('group_name', notnull=True, requires=[IS_NOT_IN_DB(db,
'group_of_events.group_name'), IS_SLUG()]),
    format='%(group_name)s'
)

db.define_table(
    'event',
    Field('event_name', notnull=True),
    Field('group_id', 'reference db.group_of_events', notnull=True,
requires=IS_IN_DB(db, db.group_of_events, '%(group_name)s')),
    format='%(event_name)s'
)

# controllers\group.py

def d():
    group = db(db.group_of_events.group_name ==
request.args(0)).select(db.group_of_events.ALL, db.event.ALL,
left=db.event.on(db.event.group_id ==
db(db.group_of_events.id).select(db.group_of_events.id)),
orderby=db.group_of_events.group_name) or redirect(URL('', 'groups'))
    return dict(group=group)

--