SQLAlchemy |对于一对多关系，将联接结果限制为一行

问题描述

我有两个实体:项目"和学生名单".一个项目可以有许多学生名单.

I have two entities: Projects and Student Lists.One Project can have many Student lists.

我正在尝试加入项目中的学生列表，并且仅根据学生列表的自定义顺序返回每个项目的第一行.

I am attempting to join student list on project and only return the first row for each project based on custom ordering of student lists.

尝试的子查询:

_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定义自定义排序.该函数返回子查询，然后我尝试将其添加到下面的项目externalquery中(student_list_subquery指的是上面返回的内容):

Above I define the custom ordering based on student list status id. The function returns the subquery which I then attempt to join to my Project outerquery below (student_list_subquery refers to what is returned above):

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输出

Below is the relevant SQL output

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)解决方案将无法使用，row_number/partition解决方案也将无法使用...

I am using mySQL so (Distinct On) solutions won't work and neither will row_number/partition solutions either...

我似乎在这里也遇到了同样的问题 SQLAlchemy:FROM条目仍存在于相关子查询中

I seem to be having the same issue raised here SQLAlchemy: FROM entry still present in correlated subquery

推荐答案

最终解决了该问题.希望当使用SQLAlchemy和mySQL对组进行自定义排序时，这可以帮助其他尝试解决每组第一个n"问题的人.

Finally Solved the issue. Hope this helps someone else trying to solve the first-n-per-group problem when custom ordering of group required using SQLAlchemy and mySQL.

首先，我具有此函数，该函数返回项目的最高优先级的一个student_list_status_id(因此具有过滤器).

First I have this function that returns the one student_list_status_id with highest priority for the project(hence the filter).

@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_list_status_id相关联(别名为ps)到项目中.然后，我可以对目标状态名称进行排序.

The I join project status which correlates to the student_list_status_id in the query above (aliased as ps) onto the project. Then, I can sort on the project status name which was my goal.

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

请注意，student_list_subq是指上面的create_student_list_subquery函数的结果.

Note that student_list_subq refers to result of create_student_list_subquery function above.

这篇关于SQLAlchemy |对于一对多关系，将联接结果限制为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！