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*
class AwardsAwardsCategory(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
}
class Award(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
SELECT awards.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 FROM awards_awards_categories JOIN awards
ON awards.id = awards_awards_categories.award_id LEFT OUTER JOIN
awards_awards_categories AS awards_awards_categories_1 ON awards.id =
awards_awards_categories_1.award_id WHERE awards.featured != 0 AND
awards_awards_categories.nomination != true
It is almost correct except the WHERE clause is missing a condition:
AND awards_awards_categories_1.nomination != true
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].
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.
