英文:
A table query that includes some ids and excludes others with pagination. SQLAlchemy
问题
我正在处理一个用户表格。
我有两个列表。第一个包含必须包含在答案中且必须排在前面的ID。第二个包含不应出现在答案中的ID。
在这种情况下，分页应该起作用。
我尝试将查询拆分为两个查询并使用"UNION"，它可以工作，但结果是分散的。
find_ids = [5, 6, 7]
exclude_ids = [2]
query1 = User.query.filter(User.id.in_(find_ids) & User.id.notin_(exclude_ids))
query2 = User.query.filter(User.id.notin_(find_ids) & User.id.notin_(exclude_ids))
return query1.union(query2).paginate(page=page, per_page=per_page)
在这个示例中，我期望具有如下ID的元素：[5, 6, 7, 1, 3, 4, 8, 9, 10, ...]。
英文:
I am working with a table of users.
I have 2 lists. The first one consists of IDs, which must be in the answer and must come first. The second one consists of IDs, which should not be in the answer.
In this case, pagination should work.
I tried to split the query into 2 queries and use "UNION", it works but the results are scatter.
`find_ids = [5, 6, 7]
exclude_ids = [2]
query1 = User.query.filter(User.id.in_(find_ids) & User.id.notin_(exclude_ids))
query2 = User.query.filter(User.id.notin_(find_ids) & User.id.notin_(exclude_ids))
return query1.union(query2).paginate(page=page, per_page=per_page)`
In this example, I'm expecting elements with id's like this: [5, 6, 7, 1, 3, 4, 8, 9, 10, ...]
答案1
得分: 1
以下是代码部分的中文翻译：
1 Erwin Brandstetter的这个回答指出在UNION中不能依赖子查询的顺序。相反，我们需要在UNION的结果上应用ORDER BY子句。为了根据列表成员资格获得所需的排序，我们可以在ORDER BY子句中使用CASE语句，如下所示（假设您正在使用Flask-SQLAlchemy）：
q1 = User.query.filter(
User.id.in_(find_ids) & User.id.not_in(exclude_ids)
)
q2 = User.query.filter(
User.id.not_in(exclude_ids) & User.id.not_in(find_ids)
)
subq = q1.union(q2).subquery()
q3 = (
db.session.query(subq.c.users_id)
.select_from(subq)
.order_by(
db.case(
(subq.c.users_id.in_(find_ids), subq.c.users_id - 100),
else_=subq.c.users_id,
)
)
)
print(q3.all())
纯SQLAlchemy版本如下：
with Session() as s:
q1 = sa.select(User).where(
User.id.in_(find_ids) & User.id.not_in(exclude_ids)
)
q2 = sa.select(User).where(
User.id.not_in(exclude_ids) & User.id.not_in(find_ids)
)
subq = q1.union(q2).subquery()
q3 = (
sa.select(subq.c.id)
.select_from(subq)
.order_by(
sa.case(
(subq.c.id.in_(find_ids), subq.c.id - 100), else_=subq.c.id
)
)
)
result = s.scalars(q3)
print(list(result))
英文:
This answer by Erwin Brandstetter states that you cannot rely on the order of subselects in a UNION. Instead we need to apply an ORDER BY clause to the result of the UNION. To obtain the desired ordering based on list membership we can use a CASE statement in the ORDER BY` clause, like this (I'm assuming that you are using Flask-SQLAlchemy):
q1 = User.query.filter(
User.id.in_(find_ids) & User.id.not_in(exclude_ids)
)
q2 = User.query.filter(
User.id.not_in(exclude_ids) & User.id.not_in(find_ids)
)
subq = q1.union(q2).subquery()
q3 = (
db.session.query(subq.c.users_id)
.select_from(subq)
.order_by(
db.case(
(subq.c.users_id.in_(find_ids), subq.c.users_id - 100),
else_=subq.c.users_id,
)
)
)
print(q3.all())
The pure SQLAlchemy version would be
with Session() as s:
q1 = sa.select(User).where(
User.id.in_(find_ids) & User.id.not_in(exclude_ids)
)
q2 = sa.select(User).where(
User.id.not_in(exclude_ids) & User.id.not_in(find_ids)
)
subq = q1.union(q2).subquery()
q3 = (
sa.select(subq.c.id)
.select_from(subq)
.order_by(
sa.case(
(subq.c.id.in_(find_ids), subq.c.id - 100), else_=subq.c.id
)
)
)
result = s.scalars(q3)
print(list(result))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论