时间戳

首页 > 解决方案 > SQLAlchemy：通过相互外键对两列的查询结果进行分组

python - SQLAlchemy：通过相互外键对两列的查询结果进行分组

问题描述

我正在尝试做的事情：

我在 Postgres 数据库中有 3 个表。

user
-----------------------
user_id   name      age  
-----------------------
001       Alice     25
002       Bob       32
003       Colin     31
004       Danny     40

laundry_task
-----------------------------
task_id   user_id   completed  
-----------------------------
101       001       true
102       003       false
103       001       true

cooking_task
-----------------------------
task_id   user_id   completed  
-----------------------------
201       001       true
202       002       true
203       003       false

我需要 SQLAlchemy 向我返回一个元组列表，例如：
[(001, 2, 1), (002, 0, 1)]
元组包括：
0) user_id
1) number_of_times_laundry_task_completed
2) number_of_times_cooking_task_completed

或
[(001, 3), (002, 1)]
元组包括：
0) user_id
1) sum_of_times_boths_tasks_completed

我在一个查询中需要它。 （子查询没问题）

如果其中 1 个表中没有分配任务，completed则应将 count 设置为 0。如果两个表中都没有分配任务，则元组应该看起来像(004, 0, 0)(或(004, 0)) 或根本不在列表中。

它在 2 个查询中的样子：

laundry_tasks = (
    session.query(LaundryTask.user_id, func.count(LaundryTask.completed))
           .filter(LaundryTask.completed == True)
           .group_by(LaundryTask.user_id)
           .all()
) # Results in [(001, 2)]


cooking_tasks = (
    session.query(CookingTask.user_id, func.count(CookingTask.completed))
           .filter(CookingTask.completed == True)
           .group_by(CookingTask.user_id)
           .all()
) # Results in [(001, 1), (002, 1)]

# Add some Python parsing to merge two lists of tuples into the needed format...

我尝试过并且有点效果（但不是真的）：

子查询： 

laundry_tasks_query = (
    select([LaundryTask.user_id, func.count(LaundryTask.completed)])
    .where(LaundryTask.completed == True)
    .group_by(LaundryTask.user_id)
)

cooking_tasks_query = (
    select([CookingTask.user_id, func.count(CookingTask.completed)])
    .where(CookingTask.completed == True)
    .group_by(CookingTask.user_id)
)

final_query = laundry_tasks_query.union(cooking_tasks_query).alias('completed_tasks')
completed_tasks = session.query(final_query).all()

结果：completed_tasks = [(001, 2), (001, 1), (002, 1)]
不确定是否有办法group_by在union查询执行之前进行，我所有的尝试都以Exceptions 结束。

我试过但没有奏效：

将所有内容都塞进一个查询中。

completed_tasks = (
    session.query(
         User.user_id, func.count(LaundryTask.completed), func.count(CookingTask.completed)
    )
    .filter(LaundryTask.completed == True)
    .filter(CookingTask.completed == True)
    .group_by(User.user_id)
    .all()
)

我知道，天真，并且正如预期的那样，返回一些废话 aka [(001, magic_number, magic_number), (002, magic_number, magic_number), (003, magic_number, magic_number), (004, magic_number, magic_number)]，其中magic_number是相同的整数。

如果有任何其他方法可以实现我需要的输出，我非常愿意接受建议。任何帮助表示赞赏。

标签： pythonsqlpython-3.xpostgresqlsqlalchemy

解决方案

推荐阅读