CODEWITHSUNDEEP
pythonsqlalchemypyramid
Jonathan Ong
Jonathan Ong

Reputation: 20315

Having issues creating a query with an inner join and an outer join

I have the following model (simplified):

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

class Thing(Base):
    __tablename__ = 'thing'
    id = Column(Integer, primary_key=True)

class Relationship(Base):
    __tablename__ = 'relationship'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('thing.id'))
    parent = relationship('Thing', backref='parentrelationships', primaryjoin = "Relationship.parent_id == Thing.id")
    child_id = Column(Integer, ForeignKey('thing.id'))
    child = relationship('Thing', backref='childrelationships', primaryjoin = "Relationship.child_id == Thing.id")

class Vote(Base)
    __tablename__ = 'vote'
    id = Column(Integer, primary_key=True)
    rel_id = Column(Integer, ForeignKey('relationship.id'))
    rel = relationship('Relationship', backref='votes')
    voter_id = Column(Integer, ForeignKey('user.id'))
    voter = relationship('User', backref='votes')

I wanted to query all Relationships with a certain parent, and I also want to query votes made by a certain user on those Relationships. What I've tried:

def get_relationships(thisthing, thisuser):
    return DBSession.query(Relationship, Vote).\
        filter(Relationship.parent_id == thisthing.id).\
        outerjoin(Vote, Relationship.id == Vote.rel_id).\
        filter(Vote.voter_id == thisuser.id).\
        filter(Vote.rel_id == Relationship.id).\
        all()

as well as:

def get_relationships(thisthing, thisuser):
    session = DBSession()
    rels = session.query(Relationship).\
        filter(Relationship.parent_id == thisthing.id).\ 
        subquery()
    return session.query(rels, Vote).\
        outerjoin(Vote, rels.c.id == Vote.rel_id).\
        filter(Vote.voter_id == thisuser.id).\
        all()

I get nulls when I do either of these queries. What am I doing wrong?

Upvotes: 0

Views: 105

Answers (1)

van
van

Reputation: 76992

Just turn on SQL logging (echo=True) and you will see that the resulting SQL query for the first option is something like:

SELECT relationship.id AS relationship_id, relationship.parent_id AS relationship_parent_id, relationship.child_id AS relationship_child_id, vote.id AS vote_id, vote.rel_id AS vote_rel_id, vote.voter_id AS vote_voter_id 
FROM relationship LEFT OUTER JOIN vote ON relationship.id = vote.rel_id 
WHERE relationship.parent_id = ? AND vote.voter_id = ? AND vote.rel_id = relationship.id

If you examine it, you will notice that the clause vote.rel_id = relationship.id is part of both the JOIN clause and the WHERE clause, which makes the query to filter out those Relationship rows which do not have any votes by requested user.

Solution:

  1. Remove redundant filter(Vote.rel_id == Relationship.id). part from the query.
  2. Edit-1: Also move (remove) the filter for the user filter(Vote.voter_id == thisuser.id) out of WHERE and into the LEFT JOIN clause: outerjoin(Vote, and_(Relationship.id == Vote.rel_id, Vote.voter_id == thisuser.id)).

Upvotes: 2

Related Questions