On Aug 20, 2012, at 7:08 PM, adolfo wrote: > Thanks Michael. > > The question is: how can I use that trick as a relation? > > I can do: > > session.query(Node).outerjoin(Node.right_nodes) > > and > > session.query(Node).outerjoin(Node.left_nodes) > > but not > > session.query(Node).outerjoin(Node.all_nodes) > > and that is what I'm looking for. I mean the relation() functionality. > > Is there some way to accomplish that?
the outerjoin is like this: subq = select([node_to_node.c.left_node_id.label('parent'), node_to_node.c.right_node_id.label('child')]).union(select([node_to_node.c.right_node_id, node_to_node.c.left_node_id])) nalias = aliased(Node) session.query(Node).outerjoin(subq, Node.id==subq.c.parent).outerjoin(nalias, nalias.c.id==subq.c.child) that is, a UNION in the middle. you can make a relationship() where you take that subq above and make it the "secondary" part of the relationship, if you wanted to have more of the relationship mechanics available. > > > Thanks again! > > Adolfo > > On Monday, August 20, 2012 5:35:51 PM UTC-5, Michael Bayer wrote: > > On Aug 20, 2012, at 6:07 PM, adolfo wrote: > >> I have a Self-Referential Many-to-Many Relationship situation where the >> >> right_nodes = relationship("Node", >> secondary=node_to_node, >> primaryjoin=id==node_to_node.c.left_node_id, >> secondaryjoin=id==node_to_node.c.right_node_id, >> backref="left_nodes" >> >> works fine. >> The problem: >> I need a "related nodes" relationship, which, in one expression, returns all >> related nodes, both left nodes and right nodes, excluding the given node >> itself. >> Is that possible using the RELATIONSHIP construct? > > this is the "my friends and people who I'm friends with" query and the recipe > is....well I guess I didn't put it up anywhere yet, the idea is to use a > @property: > > class MyClass(Base): > # ... > > @property > def all_nodes(self): > return self.left_nodes + self.right_nodes > > to do this in SQL only requires a UNION in a subquery. If you really wanted > that I can work it out, it's more burdensome but if you have a specific query > style in mind it could be useful. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/W5_3pjXf2V4J. > To post to this group, send email to sqlalchemy@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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.