M:N self-reference

[Wade Leftwich]

Hi,

My question is: Is it possible to have a many-to-many relation on a
self reference, without using an Association Object?

I'm using sqlalchemy to work with Drupal tables for a data migration
project. Drupal's taxonomy features hierarchical vocabularies, where
terms live in a term_data table and are associated with other terms
via term_hierarchy. A child can have many parents.

I've gotten the object mapping to basically work, like this:

class TermHierarchy(Base):
__tablename__ = 'term_hierarchy'
childid = Column(u'childid', Integer(), primary_key=True,
nullable=False)
parentid = Column(u'parentid', Integer(), primary_key=True,
nullable=False)

class TermData(Base):
__tablename__ = 'term_data'
id = Column(u'id', Integer(), primary_key=True, nullable=False,
autoincrement=True)
name = Column(u'name', String(length=255))

children_assoc = relation(TermHierarchy,
primaryjoin=(TermHierarchy.parentid==id),
foreign_keys=[TermHierarchy.parentid],
backref=backref('parent', uselist=False),
uselist=True)

parents_assoc = relation(TermHierarchy,
primaryjoin=(TermHierarchy.childid==id),
foreign_keys=[TermHierarchy.childid],
backref=backref('child', uselist=False),
uselist=True)

This works OK, except that to get to a parent or child I have to go
through the Association object:

>>> first = TermData('first')
>>> second = TermData('second', parent=first)
>>> third = TermData('third', parent=second)
>>> another_third = TermData('another_third', parent=second)
>>> second.children_assoc
[<selfref.TermHierarchy object at 0xa69c40c>, <selfref.TermHierarchy
object at 0xa69c5ac>]
>>> [x.child for x in second.children_assoc]
[<selfref.TermData object at 0xa69c48c>, <selfref.TermData object at
0xa69c56c>]
>>>

Since there are only 2 columns in the association table, i'd like to
leave it out of the mapping, which would simplify
running queries. I tried several variations on M:N mapping but
couldn't make it work. Before I bang my head against this wall any
more, maybe someone could tell me if it's possible?

Any advice appreciated.

Wade Leftwich
Ithaca, NY

[Mike Conley]

Short answer, no.

Think about it this way.

In a simple 1:n relationship,  each child has a pointer to the parent. In a m:n relationship, each child must have a pointers to many parents and those pointers must live somewhere. In a relational database, there is no way to store an arbitrary number of parent pointers in a child record; that leads to the requirement for an association table. No other way to do it.

SQLAlchemy cannot change the data modeling needed here, but constructs available in SA can make the coding easier for managing the association table.

Mike Conley

[Wade Leftwich]

I know the association table is required for the many-to-many data
model; what I'm trying to do is keep the Association Object out of my
TermData class. There's an example of how to do this in a M:N between
two different tables at http://www.sqlalchemy.org/docs/05/ormtutorial.html#building-a-many-to-many-relation,
but I can't seem to get the same behavior with Parent = aliased
(TermData).


>>> post_keywords = Table('post_keywords', metadata,
... Column('post_id', Integer, ForeignKey('posts.id')),
... Column('keyword_id', Integer, ForeignKey('keywords.id'))
... )

>>> class BlogPost(Base):
... __tablename__ = 'posts'
...
... id = Column(Integer, primary_key=True)
... user_id = Column(Integer, ForeignKey('users.id'))
... headline = Column(String(255), nullable=False)
... body = Column(Text)
...
... # many to many BlogPost<->Keyword
... keywords = relation('Keyword', secondary=post_keywords,
backref='posts')
...
... def __init__(self, headline, body, author):
... self.author = author
... self.headline = headline
... self.body = body
...
... def __repr__(self):
... return "BlogPost(%r, %r, %r)" % (self.headline, self.body,
self.author)

>>> class Keyword(Base):
... __tablename__ = 'keywords'
...
... id = Column(Integer, primary_key=True)
... keyword = Column(String(50), nullable=False, unique=True)
...
... def __init__(self, keyword):
... self.keyword = keyword

[Mike Conley]

Something like this? The association table is declared in the relationships, but never referenced when creating or accessing objects.

class Assoc(Base):

    __tablename__ = 'assoc'

    parent = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)

    child = Column(Integer, ForeignKey('m_to_n.id'), primary_key=True)

class MToN(Base):

    __tablename__ = 'm_to_n'

    id = Column(Integer, primary_key=True)

    name = Column(String)

    children = relation('MToN', secondary=Assoc.__table__,

            primaryjoin='MToN.id==Assoc.parent',

            secondaryjoin='MToN.id==Assoc.child',

            backref=backref('parents')

        )

    def __repr__(self):

        return "<M:N %s %s>" % (self.id, self.name)

    

metadata.create_all()

compile_mappers()

p1 = MToN(name='P1')

p2 = MToN(name='P2')

p3 = MToN(name='P3')

c1 = MToN(name='C1')

c1a = MToN(name='C1A')

c2 = MToN(name='C2')

c3 = MToN(name='C3')

p1.children.append(c1)

p1.children.append(c1a)

c1.children.append(c2)

p2.children.append(c1)

c3.parents.append(p1)

c3.parents.append(p3)

session.add_all([p1, p2, p3])

session.commit()

engine.echo=False

qry_p = session.query(MToN).filter(MToN.name.like('P%'))

for p in qry_p:

    print '======'

    print p

    for ch1 in p.children:

        print '  ', ch1

        for ch2 in ch1.children:

            print '     ',ch2

[Wade Leftwich]

Something exactly like that. Thanks much.