How do I correctly create a flask sqlalchemy many to many relationship with multiple foreign keys
<p>In the Flask sqlalchemy documentation an example of using a simple many to many relationship is given:</p>

<pre><code>tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'))
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags,
        backref=db.backref('pages', lazy='dynamic'))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
</code></pre>

<p>Where one can use the following syntax to reach out to the related objects:</p>

<pre><code>Page.tags
</code></pre>

<p>What I am trying to accomplish is basically to add the relationship below to the one above: </p>

<pre><code>tag_children = db.Table('tag_children,',
                        db.Column('parent_id', db.Integer, db.ForeignKey('tags.tag_id')),
                        db.Column('child_id', db.Integer, db.ForeignKey('tags.tag_id'))
                        )
</code></pre>

<p>So that each page has tags attached to it, but each tag can have multiple children in the scope of that page. I've made a show case for a Page called <strong>cars</strong> below where I have it's tags and their respective children tags.</p>

<p>(Page) Cars:</p>

<ul>
<li>Mercedes

<ul>
<li>A-series</li>
<li>B-series</li>
<li>C-series</li>
</ul></li>
<li>Tesla

<ul>
<li>Tesla Roadster</li>
<li>Model X</li>
<li>Model S</li>
<li>Model 3</li>
</ul></li>
</ul>

<p><em>All the list items above are tag objects</em> </p>

<p>I want to be able to use the following syntax bellow to get the related objects:</p>

<pre><code>Page.tag.children
</code></pre>

<p>For instance (obvously dummy code below, but I want to be clear about what the intended purpose of the relationship is):</p>

<pre><code>Cars.tesla.children
</code></pre>

<p>I think, you don't need another table for <code>tag_children</code>. Try to use <a href="http://docs.sqlalchemy.org/en/latest/orm/self_referential.html" rel="nofollow">SQLAlchemy</a> Adjacency Lists:</p>

<pre><code>class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('tag.id'))
    children = relationship("Tag",
                backref=backref('parent', remote_side=[id])
            )
</code></pre>

<p>With this schema you may use syntax like:</p>

<pre><code>for tag in page.tags:  # where page is a Page instance received from db
    print tag.children
</code></pre>

<p>It is a common syntax for working with SQLAlchemy models. Try to use it instead of the proposed <code>Cars.tesla.children</code>. </p>

<p>Something like <code>Cars['tesla'].children</code> may be implemented via <a href="https://docs.python.org/2/reference/datamodel.html#object.__getitem__" rel="nofollow"><strong>getitem</strong></a> method, but i think, it's very unclear way.</p>

<p>Full code snippet:</p>

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

    __tablename__ = 'page'

    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    tags = relationship('Tag', secondary='tags',
        backref=backref('pages', lazy='dynamic'))

    def __str__(self):
        return self.name


class Tag(Base):

    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    parent_id = Column(Integer, ForeignKey('tag.id'))
    children = relationship(
        "Tag",
        backref=backref('parent', remote_side=[id])
    )

    def __str__(self):
        return self.name

class Tags(Base):
    __tablename__ = 'tags'

    tag_id = Column(Integer, ForeignKey('tag.id'), primary_key=True)
    page_id = Column(Integer, ForeignKey('page.id'), primary_key=True)
</code></pre>

<p>And test case:</p>

<pre><code># Create page and tags
session.add(Page(id=1, name="cars"))
session.add(Tag(id=1, name="Mercedes"))
session.add(Tag(id=2, name="A-series", parent_id=1))
session.add(Tag(id=3, name="B-series", parent_id=1))
session.add(Tag(id=4, name="C-series", parent_id=1))
session.add(Tag(id=5, name="Tesla"))
session.add(Tag(id=6, name="Tesla Roadster", parent_id=5))
session.add(Tag(id=7, name="Model X", parent_id=5))
session.add(Tag(id=8, name="Model S", parent_id=5))
session.add(Tag(id=9, name="Model 3", parent_id=5))

# Fill relation
session.add(Tags(tag_id=1, page_id=1))
session.add(Tags(tag_id=5, page_id=1))

session.commit()

print session.query(Page).get(1)  # &gt;&gt;&gt; cars
print session.query(Page).get(1).tags  # &gt;&gt;&gt; Mercedes, Tesla
print session.query(Page).get(1).tags[1].children  # &gt;&gt;&gt; Tesla models
</code></pre>

python sqlalchemy relational-database flask-sqlalchemy