SQLAlchemy Many-to-Many Relationship on a Single Table Error: NoReferencedTableError

Question

I'm using Flask and SQLAlchemy.

I'm trying to reference column in the same table to create a tree structure, using additional table to store all ancestor-child connections, not only parent-child.

I've used the example here https://stackoverflow.com/a/5652169/2947812, and my code in models.py is:

# -*- coding: utf-8 -*-
from my_app import db    

Cats_hierarchies = db.Table('cats_hierarchies', db.MetaData(),
        db.Column('parent_id', db.Integer, db.ForeignKey('category.id')),
        db.Column('category_id', db.Integer, db.ForeignKey('category.id'))
        )

class Category(db.Model):
    __tablename__ = 'category'

    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship("Category",
        secondary = Cats_hierarchies,
        primaryjoin = Cats_hierarchies.c.category_id == id,
        secondaryjoin = Cats_hierarchies.c.parent_id == id,
        backref="children")

When I try to create database with from my_app import db I receive the following error message: sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'cats_hierarchies.category_id' could not find table 'category' with which to generate a foreign key to target column 'id'

What am I doing wrong?

Answer 1

I got rid of the error by adding

foreign_keys =
   [Cats_hierarchies.c.parent_id,
   Cats_hierarchies.c.category_id])

to Category.children definition.

Still don't know though why is it necessary. Found it here: https://stackoverflow.com/a/8809907/2947812