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.