srmm.py

Go to the documentation of this file.

"""
Take self-referential many-to-many for a spin
   http://www.sqlalchemy.org/docs/orm/relationships.html#self-referential-many-to-many-relationship


SRMM vs SR1M
~~~~~~~~~~~~~

Self referential one-to-many example ... familiar tree structure
  * unix "Directory" has single parent "Directory", and multiple child "Directory"

Self referential many-to-many example for "People" 
   * some have friends (symmetrical relationship)   
   * some have idols (asymmetrical relationship) 
   * others have stalkers (asymmetrical relationship) 

subsets and supersets with all manner of possible containments 


.. code-block: ipython


        n [2]: from NonDbi import Node

        In [3]: n = Node("color")

        In [5]: n. 
        n.__class__             n.__format__            n.__mapper__            n.__reduce_ex__         n.__str__               n.__weakref__           n.id                    n.right_nodes           
        n.__delattr__           n.__getattribute__      n.__module__            n.__repr__              n.__subclasshook__      n._decl_class_registry  n.label                 
        n.__dict__              n.__hash__              n.__new__               n.__setattr__           n.__table__             n._sa_class_manager     n.left_nodes            
        n.__doc__               n.__init__              n.__reduce__            n.__sizeof__            n.__tablename__         n._sa_instance_state    n.metadata              

        In [5]: n.left_nodes
        n.left_nodes

        In [5]: n.left_nodes
        Out[5]: []

        In [6]: n.left_nodes.append(Node("red"))

        In [7]: n.left_nodes.append(Node("green"))

        In [8]: n.left_nodes.append(Node("blue"))

        In [9]: n.left_nodes
        Out[9]: [Node('red'), Node('green'), Node('blue')]

        In [10]: session
        Out[10]: <sqlalchemy.orm.session.Session object at 0x94cdb6c>

        In [11]: session.add(n)

        In [12]: session.commit()



Note that the colors are not to session individually, they come
via their association::

        mysql> select * from node_to_node ;
        +--------------+---------------+
        | left_node_id | right_node_id |
        +--------------+---------------+
        |            3 |             2 | 
        |            4 |             2 | 
        |            5 |             2 | 
        +--------------+---------------+
        3 rows in set (0.00 sec)

        mysql> select * from node ;
        +----+-------+
        | id | label |
        +----+-------+
        |  1 | root  | 
        |  2 | color | 
        |  3 | red   | 
        |  4 | green | 
        |  5 | blue  | 
        +----+-------+
        5 rows in set (0.00 sec)




Compare with Django recursive M2M to self
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.ManyToManyField.symmetrical

Django symetrical m2m to self::

    class Person(models.Model):
        friends = models.ManyToManyField("self")

When Django processes this model, it identifies that it has a ManyToManyField on itself, and as a result, 
it doesn't add a person_set attribute to the Person class. 
Instead, the ManyToManyField is assumed to be symmetrical -- that is, if I am your friend, then you are my friend.

If you do not want symmetry in many-to-many relationships with self, set symmetrical to False. 
This will force Django to add the descriptor for the reverse relationship, 
allowing ManyToManyField relationships to be non-symmetrical. 

Presumably that means can do:

    class Person(models.Model):
        friends = models.ManyToManyField("self")
        idols = models.ManyToManyField("self", symetrical=False )



 
"""
from sqlalchemy import Integer, ForeignKey, String, Column, Table
from sqlalchemy.orm import relationship
from meta import Base

node_to_node = Table("node_to_node", Base.metadata,
    Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
    Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
)

class Node(Base):
    """



    """
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    label = Column(String(255))
    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"
    )

    def __init__(self, label ):
        self.label = label
    def __repr__(self):
        return "Node('%s')" % self.label