1

I have a model X and a model Y.

Y contains a foreign key reference to X.id, with a instance of the related X entry available by the property x. 

x_id = Column(Integer, ForeignKey('xtable.id'))    
x = relationship('X')

X also has a boolean property 'publish'.

Executing a query on Y, how can i filter my results to those where x.publish is True;

Ive tried doing something like this:

DBSession.query(Y).filter_by(x.publish = True).all()

But this doesnt work, i get an error saying keyword cant be an expression. Ive looked through the sql alchemy docs for a solution, but i cant seem to find what im looking for. Any suggestions?

richzilla
  • 40,440
  • 14
  • 56
  • 86

2 Answers2

3

You need to add a join in your query to class X and use filter instead of filter_by:

qry = DBSession.query(Y)
qry = qry.join(X)
qry = qry.filter(X.publish == True)
qry.all()

or in one go:

DBSession.query(Y).join(X).filter(X.publish == True).all()

See this question regarding the difference between using filter and filter_by.

Community
  • 1
  • 1
Mark Gemmill
  • 5,889
  • 2
  • 27
  • 22
3

Other option is

You can create direct new relationship

Like

x1 = relationship(X,
             primaryjoin='and_(X.id==Y.x_id, X.publish==True)'
             )

This will do join automatically.

Nilesh
  • 20,521
  • 16
  • 92
  • 148