[web2py] Simplifying a SQL statement

[Adrian Edwards]

I'm trying to figure out a way to simplify a series of SQL statements.

I have the following 2 tables

db.define_table('event_users',
    Field('event', 'reference events'),
    Field('user_name', 'reference auth_user'),
    Field('goal', 'double'),
    Field('last_entry', 'date'),
    format='%(user_name)s')

db.define_table('entries',
    Field('user', 'reference event_users'),
    Field('date_entered', 'date'),
    Field('value', 'double'),
    format='%(user)s %(date_entered)s')

A user is able make multiple entries per event and I'm trying come up
with select statement that gives me the last entry for each user .

I also need to maintain a history of entries to display on a different
page.
So if a user makes the following entries
11/1/2011  100
11/2/2011  200
11/3/2011  300

I have a summary page that will give me the latest entry (in this case
300) and then a detail page that will show all 3 entries.

The following works for the most part but its long and it returns a
dict inside a row inside a list and is very difficult to display the
individual fields.

# get users registered for the event
    user_query = db.event_users.event == event_id
    users = db(user_query).select()

# get the last entry for each user
    entries = []
    for user in users:
        entry_query = db.entries.user == user
        entry =
db(entry_query).select(orderby=db.entries.date_entered).last()
        entries.append(entry)

Any suggestions?
Thanks.

Adrian