python-3.x - 带有条件过滤器和结果的 SQLAlchemy 查询

Question

我正在构建一个 fastAPI 应用程序，并且我有一个复杂的查询，我试图避免将其作为多个单独的查询来连接结果。我有以下所有表都有外键：

更改日志： change_id | original (FK ROSTER.shift_id) | new (FK ROSTER.shift_id) | change_type (FK CONFIG_CHANGE_TYPES)

名册： shift_id | shift_type (FK CONFIG_SHIFT_TYPES) | shift_start | shift_end | user_id (FK USERS)

CONFIG_CHANGE_TYPES： change_type_id | change_type_name

CONFIG_SHIFT_TYPES： shift_type_id | shift_type_name

用户： user_id | user_name

FK= 外键

我需要返回以下信息： user_name、change_type_name和shift_start shift_end以及shift_type_name那些 shift_id 与 CHANGE_LOG 行中的原始或新匹配的人。

问题是 CHANGE_LOG 表可能既有原始的也有新的，只有原始的但没有新的，或者只有新的但没有原始的。但是由于用户可以在提交请求之前从下拉框中选择一些选项，因此我还需要能够包含一个过滤器来挑出：

1. 只有一个用户，或所有用户
2. 任何 change_type，或一组 change_types

问题是我无法找到一种方法来保证每一行的 user_name 而不事后检查它，因为我不知道新的或原始的是否存在或设置为null.

SQLalchemy 中有没有办法在查询中有一个可选的过滤器，我可以说如果原始存在使用它来获取 user_id，但如果不存在则使用新的来获取 user_id。此外，如果我有一个查询肯定会找到那些具有原始和新班次的人，它永远不会找到那些只有其中一个班次的人，因为条件永远不会匹配。

我也读过这个和类似的，虽然他们会解决有条件地设置一些过滤器的问题，但它并没有解决部分空值根本不返回任何内容，而不是一半数据的问题。 这个似乎解决了这个问题，但我不知道如何实现它。

我知道这很复杂，所以如果我在解释问题方面做得不好，请告诉我。

标签： python-3.xsqlalchemyfastapi

Answer 1

排序。解决方案是使用该outerjoin选项。如果我在定义每个类时正确地添加关系，我确信语法可以比我的解决方案更优雅，但我最终得到的是明确的，我认为它更容易阅读......至少对我来说。

由于我在同一个查询中多次使用几个表来获取不同的信息，因此为这些表起别名很重要，否则我最终会遇到冲突（您想要哪个 'user_id' - 目前尚不清楚）。对于那些在家玩的人，这是我的一般解决方案：

new=aliased(ROSTER)
original=aliased(ROSTER)
o_name=aliased(CONFIG_SHIFT_TYPES)
n_name=aliased(CONFIG_SHIFT_TYPES)

pd.read_sql(
  db.query(
    CHANGE_LOG.change_id,
    CHANGE_LOG.created,
    CONFIG_CHANGE_TYPES.change_name,
    o_name.shift_name.label('original_type'),
    n_name.shift_name.label('new_type'),
    OPERATORS.operator_name
  )
  .outerjoin(original, original.shift_id==CHANGE_LOG.original_shift)
  .outerjoin(new, new.shift_id==CHANGE_LOG.new_shift)
  .outerjoin (CONFIG_CHANGE_TYPES,CONFIG_CHANGE_TYPES.change_id==CHANGE_LOG.change_type)
  .outerjoin(CONFIG_SHIFT_TYPES, CONFIG_SHIFT_TYPES.shift_id==new.roster_shift_id)
  .outerjoin(o_name, o_name.shift_id==original.roster_shift_id)
  .outerjoin(n_name, n_name.shift_id==new.roster_shift_id)
  .outerjoin(USERS, or_(USERS.operator_id==original.user_id, USERS.user_id==new.user_id)
  ).statement, engine)