Figured it out.
Basically what I was trying to do was the following select
SELECT entries FROM entries, event_users WHERE event_users.event
== event_id
Which I translated into
entries_query = db.event_users.event == event_id
entries = db(entries_query).select(db.entries.ALL,
groupby=db.entries.user)
I've still got to do some testing on it, but sure did take a long time
to figure out for something that looks so simple.
On Nov 19, 11:57 am, Adrian Edwards <[email protected]> wrote:
> 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
