Re: [sqlalchemy] Self-Referential Many-to-Many Relationship: related nodes
Thanks. The subquery approach works fine. My class is named 'Caso' (Case, in english) I included at the mapper: subq2=aliased(subq1) CasoMapper = mapper(Caso, caso, properties= { 'LinkedNodes':relation(Caso, secondary=subq2, primaryjoin=caso.c.id == subq2.c.id1, secondaryjoin=subq2.c.id2==caso.c.id), . where id1 and id2 works like the left and right links Given an instance of caso, lets say c96=session.query(Caso).filter(Caso.id==96)[0] I obtain c96.LinkedNodes == [Caso:25, Caso:26, Caso:28, Caso:65] which is great. That is what is expected. but when trying Caso2=aliased(Caso) q=session.query(Caso.id).join(Caso2,Caso.LinkedNodes).add_column(Caso2.id) and compiles nicely: print q SELECT caso.id AS caso_id, caso_1.id AS caso_1_id FROM caso JOIN (SELECT caso_vinculo.caso_1_id AS id1, caso_vinculo.caso_2_id AS id2 FROM caso_vinculo UNION SELECT caso_vinculo.caso_2_id AS caso_2_id, caso_vinculo.caso_1_id AS caso_1_id FROM caso_vinculo) AS anon_1 ON caso.id = anon_1.id1 JOIN caso AS caso_1 ON anon_1.id2 = caso_1.id BUT q.all() and I get: Traceback (most recent call last): File pyshell#37, line 1, in module q3.all() File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2115, in all return list(self) File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 2351, in instances labels) for row in fetch] File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 3342, in proc return row[column] File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2616, in __getitem__ processor, obj, index = self._parent._key_fallback(key) File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 2835, in _key_fallback expression._string_or_unprintable(key)) NoSuchColumnError: Could not locate column in row for column '%(67064688 caso)s.id' strange any idea? thanks for all this great effort. Adolfo On Monday, August 20, 2012 10:04:59 PM UTC-5, Michael Bayer wrote: 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 iswell 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 sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/n2qirzTZY60J. To post to this group, send email to
[sqlalchemy] Self-Referential Many-to-Many Relationship: related nodes
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? Adolfo -- 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/-/28NZQAdDx6sJ. 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.
Re: [sqlalchemy] Self-Referential Many-to-Many Relationship: related nodes
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 iswell 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 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.
Re: [sqlalchemy] Self-Referential Many-to-Many Relationship: related nodes
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? 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 iswell 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.
Re: [sqlalchemy] Self-Referential Many-to-Many Relationship: related nodes
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 iswell 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.