SQLAlchemy map to list related objects

user_7728273 0
Viewed 97

I have a problem with mapping relation between two entities in SQLAlchemy. I am trying to achieve the next behaviour -> map to User all related Feedbacks. The relation is by receiver_id on the other side, which is equal to user_id. So, User 1 is sending a feedback for User 2 and I am trying to get them as a list when I get User 2 object. I have next code

class User(Base):
    __tablename__ = "user"
    user_id = sa.Column('user_id', UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    first_name = sa.Column('first_name', sa.String)
    last_name = sa.Column('last_name', sa.String)
    phone_number = sa.Column('phone_number', sa.String(20), unique=True)
    email = sa.Column('email', sa.String, unique=True)
    is_active = sa.Column('is_active', sa.Boolean, server_default=expression.true(), nullable=False)
    deleted = sa.Column('deleted', sa.Boolean, server_default=expression.false(), nullable=False)
    created_time = sa.Column('created_time', sa.DateTime, server_default=func.now(), nullable=False)
    updated_time = sa.Column('updated_time', sa.DateTime, server_default=func.now(), nullable=False)
    deleted_time = sa.Column('deleted_time', sa.DateTime)
    feedbacks = relationship('UserFeedback', primaryjoin='User.user_id == UserFeedback.receiver_id', uselist=True, lazy='dynamic', backref='user_feedback')


class UserFeedback(Base):
    __tablename__ = "user_feedback"
    id = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True)
    sender_id = sa.Column(UUID(as_uuid=True), ForeignKey("user.user_id"), nullable=False)
    receiver_id = sa.Column(UUID(as_uuid=True), ForeignKey("user.user_id"), nullable=False)

    sender = relationship("User", foreign_keys=[sender_id])
    receiver = relationship("User", foreign_keys=[receiver_id])

    rating = sa.Column('rating', sa.DECIMAL, nullable=False)
    feedback = sa.Column('feedback', sa.String, nullable=False)
    date = sa.Column('date', sa.DateTime, server_default=func.now(), nullable=False)

I've filled the database with user feedbacks but when I pull user ORM doesn't map related feedbacks. It actually doesn't even have this field, so that's a little confusing

I am learning SQLAlchemy, so would appreciate help or recommendation on that

sqlalchemy postgresql orm python
1 Answers

The thing is that you're using dynamic loading strategy for relationship (the docs are here).

When querying with lazy = 'dynamic', SQLAlchemy generates a separate query rather than fetching actual results. So in your initial case feedbacks attribute contains another select statement (of AppenderQuery type) you can execute manually.

To simply get the related data just change 'dynamic' to 'joined'.

You can read more about relationship loading strategies in the docs or in this beautiful post.

user_77322000
Related