sqlalchemy |将一对多关系的连接结果限制为一行

hfsqlsce 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(410)

我有两个实体：项目和学生名单。一个项目可以有许多学生名单。
我正在尝试加入项目的学生名单，并只返回每个项目的第一行基于学生名单的自定义排序。
尝试的子查询：

_whens = {ProjectStatus.APPROVED: 1, ProjectStatus.REJECTED: 2, 
          ProjectStatus.SUBMITTED: 3, None: 4}
sort_order = case(value=StudentList.student_list_status_id, whens=_whens)

return self._session.query(StudentList).
        filter(StudentList.student_list_id==Project.project_id)
       .order_by(sort_order).limit(1).subquery()

上面我基于学生列表状态id定义了自定义顺序。该函数返回子查询，然后我尝试将其加入下面的项目outerquery（学生列表子查询指上面返回的内容）：

projects = self._session.query(models.Project)
            .filter(models.Project.project_year == year)
            .join(student_list_subquery,
            student_list_subquery.c.project_id==Project.project_id)
            .all()

下面是相关的sql输出

FROM project 
LEFT OUTER JOIN (SELECT student_list.project_id AS project_id, 
 student_list.student_list_id AS student_list_id
 FROM student_list, project
 WHERE project.project_id = student_list.project_id 
 ORDER BY CASE student_list.student_list_status_id WHEN 102 THEN 1 
 WHEN 105 THEN 2 WHEN 101 THEN 3 WHEN NULL THEN 4 END
 LIMIT 1) AS anon_1 ON anon_1.project_id = project.project_id

我使用的是mysql，所以（distinct on）解决方案不起作用，行/分区解决方案也不起作用。。。
我似乎在这里提出了同样的问题sqlalchemy：from条目仍然存在于相关子查询中

mysql python flask-sqlalchemy sqlalchemy

来源：https://stackoverflow.com/questions/50109243/sqlalchemy-limit-join-result-to-one-row-for-one-to-many-relationship

1条答案

按热度按时间

jfewjypa1#

终于解决了问题。希望这能帮助其他人在使用sqlalchemy和mysql自定义组排序时解决第一个n-per-group问题。
首先，我有一个函数，它返回一个student\u list\u status\u id，该id对项目具有最高优先级（因此是过滤器）。

@staticmethod
def create_student_list_subquery(session):
    '''create a correlated subquery that will limit result to one student
    list per project with custom sorting to retrieve highest priority list
    per project based on status'''

    sl2=aliased(StudentList)
    list_id = sl2.student_list_status_id.label("list_id")
    _whens = {ProjectStatus.APPROVED: 1, ProjectStatus.REJECTED: 2, 
             ProjectStatus.SUBMITTED: 3, None: 4}
    sort_order = case(value=list_id, whens=_whens)

    return session.query(list_id).filter(sl2.project_id==Project.project_id)
                  .order_by(sort_order)
                  .limit(1)

与上面查询中的student\u list\u status\u id相关的i join项目状态（别名为ps）添加到项目中。然后，我可以排序的项目状态名称，这是我的目标。

self._session.query(models.Project)
        .filter(models.Project.project_year == year)
        .join(ps, ps.project_status_id==student_list_subq)
        .all()

请注意，student\u list\u subq是指上面的create\u student\u list\u subquery函数的结果。

赞(0）回复(0）举报 2021-06-21

我来回答

sqlalchemy |将一对多关系的连接结果限制为一行

1条答案

相关问题

热门标签

最新问答