sql alchemy filter results on properties of joined models

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 开发者_Python百科cant seem to find what im looking for. Any suggestions?

---

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.

---

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.