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 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 sqlal...@googlegroups.com<javascript:>
> .
> 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@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.
