SQLAlchemy warning "Coercing Function object into a select() for use in IN()"

Viewed 14

I'm trying to select from the below ThingsToDo model based on allowed timezones set for a UserAccount. The UserAccount model stores a list of allowed_time_zones and the ThingsToDo model has the timezone when the thing can be ran.

class UserAccount(Base):
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    email = Column(String, index=True, nullable=False, unique=True)
    allowed_time_zones = Column(ARRAY(Text()), index=True)
    account_status = Column(Boolean())


class ThingsToDo(Base):
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(
        UUID(as_uuid=True), ForeignKey("useraccount.id"), nullable=False
    )
    allowed_timezone = Column(Text(), index=True)
    time_next_thing_allowed_utc = Column(db.DateTime, default=datetime.utcnow)


class Campaigns(Base):
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(
        UUID(as_uuid=True), ForeignKey("useraccount.id"), nullable=False
    )
    campaign_running = Column(Boolean())

The query I have written actually works, but I get the following warning

SAWarning: Coercing Function object into a select() for use in IN(); please pass a select() construct explicitly
  (ThingsToDo.allowed_timezone.in_(func.unnest(UserAccount.allowed_time_zones))) |
db.session.query(UserAccount.id, ThingsToDo.id) \
            .filter(UserAccount.id == ThingsToDo.user_id, 
                    Campaigns.user_id == UserAccount.id,
                    ) \
                    .filter(
                        (ThingsToDo.allowed_timezone.in_(func.unnest(UserAccount.allowed_time_zones))) |
                            (ThingsToDo.allowed_timezone == None) ) \
                    .filter(UserAccount.account_status == True) \
                    .filter(Campaigns.campaign_running == True) \
                    .filter(datetime.utcnow() > ThingsToDo.time_next_thing_allowed_utc ) \
                    .distinct(ThingsToDo.id) \
                    .all()

I'm not really sure what this error means or how to avoid it? I'm also not sure if this is the best way to perform this query, I would guess there is a more elegant way? Sometimes the query fails and the query gets closed, I assume by postgres.

0 Answers
Related