jgarbers wrote:
> Hi! I've posted this question over on Stack Overflow -- thought
> perhaps the mailing list might be a better resource for help. (If
> you're a Stack Overflow user and want the points, here's the link:
> http://stackoverflow.com/questions/1791713/creating-container-relationship-in-declarative-sqlalchemy
> )
>
> My Python / SQLAlchemy application manages a set of nodes, all derived
> from a base class Node. I'm using SQLAlchemy's polymorphism features
> to manage the nodes in a SQLite3 table. Here's the definition of the
> base Node class:
>
> class Node(db.Base):
> __tablename__ = 'nodes'
> id = Column(Integer, primary_key=True)
> node_type = Column(String(40))
> title = Column(UnicodeText)
> __mapper_args__ = {'polymorphic_on': node_type}
> and, as an example, one of the derived classes, NoteNode:
>
> class NoteNode(Node):
> __mapper_args__ = {'polymorphic_identity': 'note'}
> __tablename__ = 'nodes_note'
> id = Column(None,ForeignKey('nodes.id'),primary_key=True)
> content_type = Column(String)
> content = Column(UnicodeText)
>
> Now I need a new kind of node, ListNode, that is an ordered container
> of zero or more Nodes. When I load a ListNode, I want it to have its
> ID and title (from the base Node class) along with a collection of its
> contained (child) nodes. A Node may appear in more than one ListNode,
> so it's not a proper hierarchy. I would create them along these lines:
>
> note1 = NoteNode(title=u"Note 1", content_type="text/text",
> content=u"I am note #1")
> session.add(note1)
>
> note2 = NoteNode(title=u"Note 2", content_type="text/text",
> content=u"I am note #2")
> session.add(note2)
>
> list1 = ListNode(title=u"My List")
> list1.items = [note1,note2]
> session.add(list1)
>
>
>From the "Node may appear in more than one ListNode" bit, I gather this
is a many-to-many relationship, so you need a secondary table to link
ListNodes with Nodes. Also, because it is an ordered list, your
secondary table needs a position/index column. Having an ordered
many-to-many relationship can be tricky. When both sides of the
relationship have a common base class, this is even more tricky. Here is
one way you can define your secondary table and ListNode:
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.orderinglist import ordering_list
class ListNode(Node):
__mapper_args__ = {'polymorphic_identity': 'list'}
__tablename__ = 'nodes_list'
id = Column(Integer, ForeignKey('nodes.id'), primary_key=True)
contents = relation(
'ListNodeContents',
backref='list_node',
cascade='all,delete-orphan',
collection_class=ordering_list('position'),
order_by=lambda: [ListNodeContents.position],
primaryjoin=lambda: (
ListNodeContents.list_node_id == ListNode.__table__.c.id))
nodes = association_proxy(
'contents',
'node',
creator=lambda node: ListNodeContents(node=node))
# Usually you do not need a separate mapped class for a many-to-many
# relationship: you would just define a table. However, when we add the
# 'position' column to support ordering_list, we need a real mapped class.
class ListNodeContents(Base):
__tablename__ = 'nodes_list_contents'
list_node_id = Column(
Integer,
ForeignKey('nodes_list.id'),
primary_key=True)
node_id = Column(
Integer,
ForeignKey('nodes.id'),
primary_key=True)
position = Column(Integer, nullable=False)
node = relation("Node")
You can then access a ListNode's contents using my_list_node.nodes.
> The list of children should only consist of Node objects -- that is,
> all I need is their base class stuff. They shouldn't be fully realized
> into the specialized classes (so I don't get the whole graph at once,
> among other reasons).
>
You don't need to worry about loading the whole graph at once; you have
to go out of your way to do that (eagerloading). And unless you enable
polymorphic loading (using with_polymorphic method of the Query object
or mapper parameter), you will only load base class data when loading
the relation. You still get instances of derived classes (which is a
good thing), but their derived-table data will not be loaded until you
try to access those specific columns.
Hope it helps,
-Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
