web2py Select min value from join

Question

How do I write the corresponding web2py statement for the following query:

select auth_user.id, min(auth_group.ranks) as highest_gr 
from auth_user
left join auth_membership on auth_user.id = auth_membership.user_id,
left join auth_group on auth_membership.group_id = auth_group.id

I haven't written pure SQL in a while, there must be a group by somewhere but the idea is there.

Edit: I'm trying to retrieve all records from auth_user with their corresponding highest group ranks.

Answer 1

I used the following code:

min_val = db.auth_group.ranks.min()
user_highest_ranks = dict((i.auth_user.id, i._extra[min_val]) \
    for i in db( (query) &
                 (db.auth_membership.user_id==db.auth_user.id) &
                 (db.auth_group.id==db.auth_membership.group_id) ).select(
                    db.auth_user.id, min_val, groupby=db.auth_user.id
        )
    )

query is defined somewhere as query = (db.auth_user.id >0)