CODEWITHSUNDEEP
pythonsqlalchemy
NoPyGod
NoPyGod

Reputation: 5067

SqlAlchemy: Join onto another object

My little website has a table of comments and a table of votes. Each user of the website gets to vote once on each comment.

When displaying comments to the user, I will select from the comments table and outerjoin a vote if one exists for the current user.

Is there a way to make a query where the vote will be attached to the comment through comment.my_vote ?

The way I'm doing it now, the query is returning a list for each result - [comment, vote] - and I'm passing that directly to my template. I'd prefer if the vote could be a child object of the comment.

Upvotes: 0

Views: 257

Answers (2)

NoPyGod
NoPyGod

Reputation: 5067

In the end I decided that working with the tuple returned by the query wasn't a problem.

Upvotes: 0

van
van

Reputation: 76992

Setup your model to add a One-to-one relationship. Sample code from the link verbatim:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, backref="parent", 
        # lazy='joined', # @note: optional: uncomment this to have the 'child' always loaded when the parent is loaded.
    )

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

in your case Parent would be Comment and Child would be Vote.
Then you can query for Comment, at the same time eagerly loading the Vote. For this it would recomment just to uncomment the commented out line above, so that your query will return all in just one SQL statement. Alternatively you can specify loading of the Vote in the query explicitly using joinedload:

res = query(Parent).options(joinedload(Parent.child))

Upvotes: 2

Related Questions