SQLAlchemy: warning for WHERE IN condition

user_4040443 0
Viewed 1407

Recently I was fixing some bug related to pagination. It happened because of reverse join and a target rows appeared because of it. Like this:


class User(Base):
    __tablename__ = "users"

    id = sa.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
    email = Column(String, index=True, nullable=False, unique=True)



class UserPermission(Base):
    __tablename__ = "users_permissions"

    id = sa.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, index=True)
    user_id = Column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    permission = Column(String, nullable=False, index=True)

    __table_args__ = (UniqueConstraint("user_id", "permission"),)

# results = db.execute(
#     select(User).join(UserPermission).where(UserPermission.permission.in_(
#         ["can_access_first_thing", "can_access_second_thing"]
#     )
# ).unique().scalars().all()

Sometimes, a query that executes to fill results would contain more rows than existing Users because for each user there will be more than one UserPermission. That breaks any pagination that is based on offset/limit approach. My solution for this pagination problem was to query users table again after determining all required user ids:

base_query = select(User).join(UserPermission).where(UserPermission.permission.in_(
    ["can_access_first_thing", "can_access_second_thing"]
).with_only_columns(User.id)
results = db.execute(select(User).where(User.id.in_(base_query.subquery())))

Now SQLAlchemy throws a warning for this line: SAWarning: Coercing Subquery object into a select() for use in IN(); please pass a select() construct explicitly. My question is how to properly rewrite existing query so it returns the same result without throwing this warning.

sqlalchemy python
1 Answers

So I feel really dumb right now. After returning to this issue and thoroughly rereading a warning a couple of times, I've come to realize that all it says that I don't need to convert my existing select statement into subquery. Then, the fixed statements would look like this:

base_query = select(User).join(UserPermission).where(UserPermission.permission.in_(
    ["can_access_first_thing", "can_access_second_thing"]
).with_only_columns(User.id)
results = db.execute(select(User).where(User.id.in_(base_query)))

It's almost the same as before, but qwithout converting base_query to a subquery.

user_40404430
Related