英文:
web2py Select min value from join
问题
如何编写与以下查询相对应的web2py语句：
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
我已经有一段时间没有写纯SQL了，肯定需要有一个group by语句，但是思路是正确的。
编辑：我正在尝试检索auth_user中所有记录及其对应的最高组等级。
英文:
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.
答案1
得分: 0
我使用了以下代码：
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 在某处被定义为 query = (db.auth_user.id >0)。
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论