sqlalchemy join two models, which relate to other 1 model-CodePudding

I'm working on FCM notification and I need to select all my push-tokens from user_notification_tokens table with filtering by other table datetime column, but there is no relation between these two tables, but they have relation with users table.

How can I do it with sqlalchemy?

simplified schema of sqlalchemy models relations:

I've tried to do it like this:

def get_tokens()
    db: Session = next(db_service.get_session())

    x_minutes_to_event = datetime.now(pytz.utc)   timedelta(minutes=config.MINUTES_TO_PRESCRIPTION)
    tokens = [
        item[0]
        for item in db.query(models.UserNotificationsToken)\
            .join(
                models.User,
                models.User.id == models.UserNotificationsToken.user_id,
            )\
                .join(
                    models.UserPrescription,
                    models.UserPrescription.user_id == models.User.id
                )\
                    .filter(
                        models.UserPrescription.visiting_at <= x_minutes_to_event,
                        models.UserPrescription.visiting_at > datetime.now(pytz.utc),
                    )\
                        .values(column('token'))
    ]
    
    # or

    tokens = [
        item[0]
        for item in db.query(models.UserNotificationsToken)\
            .join(
                models.UserPrescription,
                models.UserNotificationsToken.user_id == models.UserPrescription.user_id,
            )\
                .filter(
                    models.UserPrescription.visiting_at <= x_minutes_to_event,
                    models.UserPrescription.visiting_at > datetime.now(pytz.utc),
                )\
                    .values(column('token'))
    ]

but I got this errors:

sqlalchemy.exc.InvalidRequestError: Don't know how to join to <Mapper at 0x7f0921681fd0; User>. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

# or

sqlalchemy.exc.InvalidRequestError: Don't know how to join to <Mapper at 0x7f54970e4970; UserPrescription>. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

CodePudding user response：

class UserNotificationToken(Base):
    __tablename__ = 'user_notification_tokens'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    token = Column(String)
    user = relationship('User', back_populates='tokens')


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

    tokens = relationship(UserNotificationToken, back_populates='user')
    prescriptions = relationship('UserPrescription', back_populates='user')

class UserPrescription(Base):
    __tablename__ = 'user_prescriptions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    visiting_at = Column(DateTime)

    user = relationship(User, back_populates='prescriptions')

metadata.create_all(engine)

class Config:
    MINUTES_TO_PRESCRIPTION = 60

with Session(engine) as session, session.begin():
    now = datetime.now(timezone.utc)
    x_minutes_to_event = now   timedelta(minutes=60)

    # Example 1 with explicit joins.
    query = session.query(
        UserNotificationToken.token
    ).join(
        User, UserNotificationToken.user_id == User.id
    ).join(
        UserPrescription, User.id == UserPrescription.user_id
    ).where(
        and_(
            UserPrescription.visiting_at <= x_minutes_to_event,
            UserPrescription.visiting_at > now))
    for (token,) in query.all():
        print (token)

    # Example 2 with joins inferred from relationships.
    query = session.query(
        UserNotificationToken.token
    ).join(
        UserNotificationToken.user
    ).join(
        User.prescriptions
    ).where(
        and_(
            UserPrescription.visiting_at <= x_minutes_to_event,
            UserPrescription.visiting_at > now))
    for (token,) in query.all():
        print (token)