How to use collection_class in SQLAlchemy?
<p>I am trying to model hierarchical and historical relations between institutional actors in SQLAlchemy (i.e. an Institution can have parents/children and predecessors/successors). What I have so far largely follows the <a href="http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html" rel="nofollow">directed graph example</a> in the SQLAlchemy docs. Now I would like to be able to access left/right neighbors to the nodes in a dictionary with <code>edge_type</code> as key and a list of nodes as values, like this: <code>node.right_nodes['edge_type']</code>.</p>

<p>I think this can be done with collection_class, but using <code>collection_class=attribute_mapped_collection('edge_type')</code> only results in one single key:value pair and not a key:[list of values].</p>

<p>Actual result:</p>

<pre><code>&gt;&gt;&gt; node.right_edges['edge_type']
&lt;Edge object&gt;
&gt;&gt;&gt; node.right_nodes['edge_type']
&lt;Node object&gt;
</code></pre>

<p>Expected result:</p>

<pre><code>&gt;&gt;&gt; node.right_edges['edge_type']
[&lt;Edge object&gt;, &lt;Edge object&gt;]
&gt;&gt;&gt; node.right_nodes['edge_type']
[&lt;Node object&gt;, &lt;Node object&gt;]
</code></pre>

<p>The model looks like this:</p>

<pre><code>from sqlalchemy import (Column, Integer, String, ForeignKey,
                        create_engine)
from sqlalchemy.orm import Session, relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection


Base = declarative_base()


class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    left_nodes = association_proxy('left_edges', 'left_node')
    right_nodes = association_proxy('right_edges', 'right_node')


class Edge(Base):
    __tablename__ = 'edge'

    left_node_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
    right_node_id = Column(Integer, ForeignKey('node.id'), primary_key=True)
    edge_type = Column(String)

    left_node = relationship(
        Node,
        foreign_keys=left_node_id,
        backref=backref(
            'right_edges',
            collection_class=attribute_mapped_collection('edge_type')
        )
    )

    right_node = relationship(
        Node,
        foreign_keys=right_node_id,
        backref=backref(
            'left_edges',
            collection_class=attribute_mapped_collection('edge_type')
        )
    )
</code></pre>

<p>Used like this:</p>

<pre><code>engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)

n1 = Node(name='LeftNode')
n2 = Node(name='RightNode1')
n3 = Node(name='RightNode2')
Edge(left_node=n1, right_node=n2, edge_type='hierarchy')
Edge(left_node=n1, right_node=n3, edge_type='hierarchy')
session.add_all([n1, n2, n3])
session.commit()

print(n1.right_nodes)  # returns dict with 1 node as value
print(n1.right_nodes['hierarchy'])  # returns 1 node
print(n1.right_edges)  # returns dict with 1 edge as value
print(n1.right_edges['hierarchy'])  # returns 1 edge
print(session.query(Edge).filter_by(left_node=n1).all())  # returns list of 2 edges
</code></pre>

<p><strong>Edit:</strong></p>

<p>The following is not an answer to my question, but to document what I have so far.</p>

<p><code>association_proxy</code> not only takes fields as the target, but you can also define properties in the target class:</p>

<pre><code>class Node(Base):

    # &lt;snip&gt;

    left_nodes = association_proxy('left_edges', 'left_nodes_edge_type')
    right_nodes = association_proxy('right_edges', 'right_nodes_edge_type')


class Edge(Base):

    # &lt;snip&gt;

    @property
    def left_nodes_edge_type(self):
        return {self.edge_type: self.left_node}

    @property
    def right_nodes_edge_type(self):
        return {self.edge_type: self.left_node}
</code></pre>

<p>This does of course not produce the desired dict of list, but a list of dict:</p>

<pre><code>&gt;&gt;&gt; node.right_nodes
[{'edge_type': &lt;Node object&gt;}, {'edge_type': &lt;Node object&gt;}]
</code></pre>

<p>You can also simply define properties on the <code>Node</code> class itself and not use <code>association_proxy</code> at all to proxy from <code>Node</code> to <code>Node</code>:</p>

<pre><code>class Node(Base):

    # &lt;snip&gt;

    @property
    def left_nodes(self):
        d = defaultdict(list)
        for edge in self.left_edges:
            d[edge.edge_type].append(edge.left_node)
        return d

    @property
    def right_nodes(self):
        d = defaultdict(list)
        for edge in self.right_edges:
            d[edge.edge_type].append(edge.right_node)
        return d
</code></pre>

<p>This allows the desired view as a dict of list:</p>

<pre><code>&gt;&gt;&gt; node.right_nodes
{'edge_type': [&lt;Node object&gt;, &lt;Node object&gt;]}
&gt;&gt;&gt; node.right_nodes['edge_type']
[&lt;Node object&gt;, &lt;Node object&gt;]
</code></pre>

<p>but this is just a convenience view.</p>