On 05/03/2010 10:33 PM, a...@vurve.com wrote: > Hi All, > > This might be a noob question, but I wasn't able to to find the answer > combing through the docs and google search. Given the following > declarations > > Base = declarative_base() > > class A(Base): > __tablename__ = 'A' > id = Column(Integer, primary_key=True) > > class B(Base): > __tablename__ = 'B' > id = Column(Integer, primary_key=True) > a_id = Column(Integer, ForeignKey('A.id')) > a = relationship('A', backref=backref('b')) > > I want to query for all A where B is not null (essentially an inner > join on A) with something like this > > session.query(A).options(joinedload('b')).filter(A.b != None) > > but it won't work because 'A.b' is a backref field. If I try > filter('A.b' != None) it won't work either. So 2 part question: > > 1) is there a better way to do an inner join like this? > 2) in general, how do you use backref fields inside of filter > criteria? > >
The problem is not due to the backref: it is because A.b is a one-to-many relationship, and "A.b != None" does not make sense for a one-to-many relationship. If you really want inner-join semantics, then you can do this (note that no filter is needed because that is inherently part of the inner join): session.query(A).join(A.b).options(contains_eager('b')) Alternatively, if you want to ensure that the database returns only one row per A, you can use a query like this, which will use an EXISTS clause: session.query(A).filter(A.b.any()) Note that the query object will filter out duplicate As on the client side in any case, so you will not notice a difference between these queries. -Conor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.