Self-Referential Many-to-Many Relationship: related nodes

[adolfo]

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

[Michael Bayer]

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.

[adolfo]

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

[Michael Bayer]

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.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Aug 21, 2012, at 4:34 PM, adolfo wrote:

> Hi Michael and all....
>
> I successfully built a "all nodes" relationship following your guidelines.
>
> As:
>
> subq1=select([caso_vinculo.c.caso_1_id.label('id1'),caso_vinculo.c.caso_2_id.label('id2')]).union(select([caso_vinculo.c.caso_2_id, caso_vinculo.c.caso_1_id]))
> 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),
> So with this property I can get both left and right linked nodes.
>
>
> with:
> CasoAlias=aliased(Caso)
> q1=session.query(Caso.id).outerjoin(CasoAlias.LinkedNodes)
> q1.all()
> works fine
> and once I add a new column from the aliased entity (CasoAlias)
> q2=q1.add_column(CasoAlias.id)
> it compiles fine
>
> SELECT caso.id AS caso_id, caso_1.id AS caso_1_id
> FROM caso AS caso_1 LEFT OUTER 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_1.id = anon_1.id1 LEFT OUTER JOIN caso ON anon_1.id2 = caso.id

there's a bug in SQLAlchemy here which I'll have fixed soon, it is somewhat hilarious though, the unusual naming scheme you're using for caso_vinculo's columns is conflicting with the labels SQLAlchemy is assigning. Note in the query, "caso_1.id AS caso_1_id" at the top. Later on, when it renders "caso_vinculo.caso_1_id" in the subquery, it's erroneously stepping on the internal column record for the "caso_1_id" symbol and breaking things.

If you use different names on the caso_vinculo table for now it should be fine.

[adolfo]

Thanks again, Michael. I guess for now I will be using a view as a workaround, yet using your core (brilliant) ideas for the issue.

Thanks a lot for all your great work.

Sqlalchemy changed my life :-)

Adolfo