加入四个涉及 LEFT JOIN 的表，没有重复 [英] Join four tables involving LEFT JOIN without duplicates

问题描述

我想将四个带有 null 值且没有重复的表连接起来，然后将其转换为 SQLAlchemy 查询.

I want to join four tables with null values and without duplicates and afterwards translate it into an SQLAlchemy query.

表格是(简化的):

Category(id, name)
Task(id, category.id, name)
User(id, name)

还有一个多对多表:

Solved(task.id, user.id)

我想获取所有任务的类别和一个列，其中包含解决该任务的特定用户:

I want to get all tasks with their category and a column with an specific user who solved the task:

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
+---------------+-----------+-----------+

目前我有 3 到 4 个单独的 SQLAlchemy 查询来执行该任务.如果可能的话，应该只合并到一个查询中，以避免对数据库进行过多的读取.

At the moment I've got 3 to 4 individual SQLAlchemy-queries to perform that task. If possible, it should be merged into only one query to avoid too many reads on the database.

到目前为止我有:

SELECT DISTINCT
  cat.name, t.name, u.name
FROM
  Task t
JOIN 
  Category cat ON cat.id = t.category_id
LEFT JOIN 
  Solved s ON s.task_id = t.id
LEFT JOIN 
  User u ON s.user_id = u.id AND
  u.name = 'luke'
ORDER BY
  cat.name

但是，虽然 DISTINCT，我从给定用户的所有行中得到了重复项:

But, although DISTINCT, I got duplicates from all rows with the given user:

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| abc           | abctask2  | <null>    | <-- duplicate
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
| ghi           | ghitask2  | <null>    | <-- duplicate
+---------------+-----------+-----------+

是否有可能通过一个查询获取该表并将其转换为 SQLAlchemy?

Is there a possibility to get this table with one query and translate it to SQLAlchemy?

推荐答案

你有两个 LEFT JOINS:

• 第一个左连接可以连接到 solved 中的多行.比如说，'jane' 和 'luke' 解决了这个任务.
• 左侧第二个加入只能加入名为luke"的用户(加入条件中为luke"！).

• The 1st left join can join to multiple rows from solved. Say, 'jane' and 'luke' solved the task.
• The 2nd left join can only join to users named 'luke' ('luke' in the join condition!).

你仍然得到两个行，'jane'只是没有显示，连接条件过滤掉了她，但LEFT JOIN保留结果中的行并附加 NULL 值.

You still get both rows, 'jane' is just not shown, the join condition filters her out, but the LEFT JOIN preserves the row in the result anyway and appends NULL values.

您可以通过使用括号和[INNER] JOIN而不是solved之间的LEFT JOIN来实现您所追求的 和 users.手册:

You can achieve what you are after by using parentheses and an [INNER] JOIN instead of the LEFT JOIN between solved and users. The manual:

如有必要，请使用括号来确定嵌套顺序.在里面没有括号，JOIN从左到右嵌套.

Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right.

SELECT c.name AS cat_name, t.name AS task_name, u.name AS user_name
FROM   task t
JOIN   category c ON cat.id = t.category_id
LEFT   JOIN
      (solved s JOIN users u ON u.id = s.user_id AND u.name = 'luke') ON s.task_id = t.id
ORDER  BY 1, 2, 3;

• 使用表名users代替保留字user.

  • Using table name users instead of the reserved word user.

    假设 users.name 定义为唯一，或者您可以有多个名为luke"的用户.

    Assuming that users.name is defined unique or you can have multiple users named 'luke'.

    如果solved中的(task.id, users.id)被定义为UNIQUE或PRIMARY KEY，你根本不需要 DISTINCT.

    If (task.id, users.id) in solved is defined UNIQUE or PRIMARY KEY, you don't need DISTINCT at all.

    结果查询不仅正确，而且速度更快.

    The resulting query is not only correct, but also faster.

    上述查询的 SqlAlchemy 版本:(由 @van 提供)
    这假设 Category、Task 和 User 是映射类，而 solved 是 Table<的实例/code>(只是一个关联表，如代码示例 多对多):

    SqlAlchemy version of the above query: (contributed by @van)
    This assumes that Category, Task and User are mapped classes, while solved is instance of Table (just an association table as shown in code example Many to Many):

    user_name = 'luke'
    q = (session.query(Category.name, Task.name, User.name)
         .select_from(Task)
         .join(Category)
         .outerjoin(
             join(solved, User,
                  (solved.c.user_id == User.id) & (User.name == user_name),
             ))
         .order_by(Category.name, Task.name, User.name)
         )
    

    这篇关于加入四个涉及 LEFT JOIN 的表，没有重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！