sql - 根据是否存在另一个表行来选择行

Question

这个标题很难写...

我的 postgresql 数据库中有 4 个表:

courses:
| id | data | created_at | updated_at | status |

course_versions:
| id | course_id | data | created_at | updated_at | status |

course_progresses:
| id | course_version_id | user_id | data | created_at | updated_at |

users:
| id | email |

一门类(class)可以有多个 course_versions，一个用户可以有 course_progresses，每个 course_progress 都关联到一个 course_version。现在，我想获取特定用户的所有 course_versions，也就是说，如果用户没有 course id: 1 的 course_version 的 course_progress，它应该返回最新的 course_version。如果用户的 course_version 为 course id: 2 的 course_progress 应该返回 course_version，无论是否有更新的 course_versions……明白了吗？ :)

这是一个有效的查询:

WITH
course_versions_with_progress AS (
  SELECT cv.*
  FROM course_versions AS cv
  INNER JOIN courses AS c ON c.id = cv.course_id
  INNER JOIN course_progresses AS cp ON cp.course_version_id = cv.id
  WHERE c.status = 1 AND cv.status = 1 AND cp.user_id = 123
),
latest_course_versions AS (
  SELECT DISTINCT ON(cv.course_id) cv.*
  FROM course_versions AS cv
  INNER JOIN courses AS c ON c.id = cv.course_id
  WHERE c.status = 1 AND cv.status = 1
  ORDER BY cv.course_id, cv.created_at DESC
)
SELECT * FROM course_versions_with_progress
UNION
  SELECT * FROM latest_course_versions
  WHERE NOT EXISTS (
    SELECT 1
    FROM course_versions_with_progress
    WHERE course_id = latest_course_versions.course_id
  )

虽然我觉得这很可怕……有没有更好的方法来编写这个查询？

Answer 1

如果没有您提供的示例数据，很难写出一些东西，但是:

with -- Example data, just to check that there are no syntax errors 
    courses(id, status) as (values
        (1,1),(2,1),(3,0)),
    course_versions(id, course_id, status, created_at) as (values
        (1,1,1,current_timestamp),(2,1,1,current_timestamp),(3,2,1,current_timestamp),(4,2,0,current_timestamp)),
    course_progresses(id, course_version_id, user_id) as (values
        (2,3,123))
select distinct on (cp.course_version_id, cv.course_id)
    cv.*
from
    course_versions as cv
        join courses as c on (c.id = cv.course_id and c.status = 1 and cv.status = 1)
        left join course_progresses cp on (cp.course_version_id = cv.id and cp.user_id = 123)
order by
    cp.course_version_id nulls last, cv.course_id, cv.created_at desc;