[sqlalchemy] Re: M:N self-reference

[Wade Leftwich]

Something exactly like that. Thanks much.

On Nov 8, 11:28 pm, Mike Conley mconl...@gmail.com wrote:
 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
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: M:N self-reference

[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

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

[sqlalchemy] Re: M:N self-reference

[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

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