[SOLVED] sqlalchemy relationship with filter

sqlalchemy relationship with filter

I am trying to get only approved responses (staged>1) but for some reason the Reply.staged>1 in the primaryjoin is not working

I am very new to sqlAlchemy so I am not really sure what the relationship for parent is doing with the remote_side

class Story(Base):

  __tablename__ = ’Story'

  id = Column(‘ID', Integer, primary_key=True)

  anonymous = Column(TINYINT)
  detail = Column('detail',String, js_name='detail')
  summary = Column('summary',String, js_name='questionSummary')
  user_id = Column('uid', Integer, ForeignKey('rlUser.uid'), js_name='userId')
  user = relationship("User")
  inspire_id = Column('inspireID', Integer, js_name='inspireId')
  staged = Column(TINYINT)

class Reply(Base):
  __tablename__ = 'Reply'
  id = Column('replyID', Integer, primary_key=True)
  parent_id = Column('parentID', Integer, ForeignKey('rlReply.replyID'),js_name='parentId')
  user_id = Column('userID', Integer, js_name='userId')
  detail = Column(String, js_name='detail')
  reply_date = Column('replyDate', TIMESTAMP, js_name='replyDate')
  expertise_type = Column('expertiseType', Integer, js_name='expertiseType')
  status = Column(Integer)
  staged = Column(Integer)
  story = relationship(“Story",primaryjoin='and_(Reply.story_id == Story.id, Reply.parent_id==0, Reply.staged>1)', backref=backref("replies", uselist=True ))
  parent = relationship('Reply',primaryjoin='and_(Reply.parent_id==Reply.id, Reply.staged>1)', backref='responses', remote_side='Reply.id', viewonly=True, uselist=True)

  def __init__(self):
    self.staged = constants.POST_QUEUE_STATUS['STAGED']

  @property
  def reply_count(self):
    return len(self.responses)

The where clause for story.replies: WHERE Reply.rushlineID = %s AND Reply.parentID = %s AND Reply.staged > %s

parms sqlalchemy.engine.base.Engine (110L, 0, 1)

Which is perfect, however: the where clause for story.replies[0].responses: WHERE Reply.parentID = %s AND %s > %s

params are sqlalchemy.engine.base.Engine (68L, 2, 1)

what I need is WHERE Reply.parentID = %s AND AND Reply.staged > %s AND %s > %s sqlalchemy.engine.base.Engine (68L, 1, 2, 1)

Solution

The magic incantation for this, I have to admit, I got to through trial and a lot of error. Here are the relevant bits:

parent_id = RlColumn('parentID', Integer, 
                     ForeignKey('rlReply.replyID'),
                     js_name='parentId')
parent = RlRelationship('Reply',
                        primaryjoin='and_(Reply.parent_id==remote(Reply.id),Reply.flagged_status <2)',
                        remote_side=id, uselist=True, 
                        foreign_keys=parent_id, backref='replies')