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
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.
