联接四个涉及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.

到目前为止，我已经得到:

So far I've got:

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"解决了任务.
• 第2个左联接只能与名为'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和users之间的LEFT JOIN来实现您的目标. 手册:

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'.

    如果在UNIQUE或PRIMARY KEY中定义了solved中的(task.id, users.id)，则根本不需要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的实例(只是一个关联表，如代码示例

    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屋！