you’d use aliased and join twice as: c1 = aliased(Child) c2 = aliased(Child)
session.query(Parent).join(c1, Parent.child1).outerjoin(c2, Parent.child2).filter(or_(c1.foo == ‘bar’, c2.foo = ‘bar’)) http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-aliases <http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-aliases> has pretty much the same form (except just one relationship). > On Dec 8, 2014, at 12:24 PM, Kevin S <kevinrst...@gmail.com> wrote: > > I am trying to construct a filter against a table that has two references to > the same child table. But I am completely confused how to go about it. I've > seen lots of examples for using aliases, but I have not been able to get any > to work. So I'll just try to give my example simply, and ask what is a > possible way to achieve what I want. > > Tables: > > class Child(): > __table__ = 'child' > child_key = Column(Integer, primary_key=True) > attr = Column(String) > > class Parent(): > __table__ = 'parent' > parent_key = Column(Integer, primary_key=True) > child_key1 = Column(Integer, ForeignKey('child.child_key')) > child_key2 = Column(Integer, ForeignKey('child.child_key')) > > child1 = relationship('Child', > primaryjoin='Child.child_key==Parent.child_key1', > uselist=False) > > child2 = relationship('Child', > primaryjoin='Child.child_key==Parent.child_key=2', > uselist=False) > > > Now, I want to filter parents by some attribute (attr) of either child1 or > child2. Naively, I would want a filter like: query.filter( > or_(child1.attr=='attribute', child2.attr=='attribute')), but I have no idea > how to reference both child1 and child2 after joining them. > > Here is how I typically join all my relationships for querying (I prefer this > syntax, because it uses the relationship definition for the join): > > query = Parent.query.join(Parent.child1).outerjoin(Parent.child2) > > It's at this point I am stuck. I'm not sure how to ask for an attribute in > 'either child1 or child2'. Also, this may not be important, but the second > join is outer, because child2 can be null. > > Any suggestions? > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
