On 11/07/2015 09:42 AM, Casper Eekhof wrote: > Dear community, > I was stuck with this issue and although I found a workaround for it, > I'm really curious if there is a solution for it in SQLAlchemy. I've > posted this question before, but on Stack Overflow > <http://stackoverflow.com/questions/33413903/filter-on-relationships/33581809#33581809>. > I later found out this group existed, so here I am :) > > *Problem* > I'm trying to filter upon a relationship. In my db I've got awards and > awards categories. I want retrieve only the featured and not nominated > awards. > > *Code* > > |classAwardsAwardsCategory(Base):__tablename__ > ='awards_awards_categories'id > =Column(Text,primary_key=True,default=generate_unique_id)awards > =relationship("Award")award_id > =Column(Text,ForeignKey("awards.id",ondelete='cascade'))nomination > =Column(Boolean)def__json__(self,request):return{"id":self.id,"nomination":self.nomination > }classAward(Base):__tablename__ ='awards'id > =Column(Text,primary_key=True,default=generate_unique_id)type > =Column(Text)featured =Column(Integer)awards > =relationship("AwardsAwardsCategory",lazy='joined')def__json__(self,request):return{"id":self.id,"type":self.type,"number_of_awards":len(self.awards),"featured":self.featured,}| > > This is the call I make: > > |query =self.session.query(Award)query > =query.join(AwardsAwardsCategory.awards)query > =query.filter(Award.featured !=0)query > =query.filter(AwardsAwardsCategory.nomination !=True)q_results =query.all()| > > This results in the following query > > |SELECTawards.id AS awards_id,awards.type AS awards_type,awards.featured > AS awards_featured,awards_awards_categories_1.id AS > awards_awards_categories_1_id,awards_awards_categories_1.award_id AS > awards_awards_categories_1_award_id,awards_awards_categories_1.nomination AS > awards_awards_categories_1_nomination FROMawards_awards_categories > JOINawards ONawards.id =awards_awards_categories.award_id LEFT OUTER > JOINawards_awards_categories AS awards_awards_categories_1 ONawards.id > =awards_awards_categories_1.award_id WHEREawards.featured > !=0ANDawards_awards_categories.nomination !=true | > > | > > It is almost correct except the WHERE clause is missing a condition: > > |AND awards_awards_categories_1.nomination !=true|
your query fails to join to the AwardsAwardsCatogory table appropriately, you need to use query.join(Award.awards) for this. It is invalid to use lazy="joined" for this purpose, please see http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html#the-zen-of-eager-loading there's also a FAQ entry at: http://docs.sqlalchemy.org/en/rel_1_0/faq/sessions.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join but the docs link is broken, fixing that now. > > How can I change my code so that it adds the last condition to the WHERE > clause. Or do I change my code entirely? > > > > | > > > -- > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to [email protected] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
