from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *


Base = declarative_base()


class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    parent_id = Column(
        Integer,
        ForeignKey('categories.id'),
        index=True,
        nullable=True
    )
    is_deleted = Column(
        Boolean,
        nullable=False,
        default=False
    )
    children = relationship(
        'Category',
        primaryjoin=(id == remote(parent_id)) & ~remote(is_deleted),
        lazy=True,
        backref=backref(
            'parent',
            primaryjoin=(remote(id) == parent_id),
            lazy=True
        )
    )

    @staticmethod
    def _create_column_prop():
        cat_alias = aliased(Category)
        cte_query = (select([cat_alias.id, cast(array([]), ARRAY(Integer)).label('parents')])
                     .where(cat_alias.parent_id.is_(None) & ~cat_alias.is_deleted)
                     .cte(recursive=True))
        parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(cat_alias.parent_id)])
                        .where((cat_alias.parent_id == cte_query.c.id) & ~cat_alias.is_deleted))
        cte_query = cte_query.union_all(parent_query)
        query = select([func.count()]).where(cte_query.c.parents.contains(array([Category.id])))
        Category.deep_children_count = column_property(query, deferred=True)


Category._create_column_prop()

e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)


root = Category(id=0, children=[
    Category(id=1),
    Category(id=2, children=[
        Category(id=3),
        Category(id=4),
        Category(id=5, children=[
            Category(id=6),
            Category(id=7)
        ])
    ])
])
s.add(root)
s.flush()
s.expire_all()

print

cat = s.query(Category).get(2)
print cat
print cat.deep_children_count  # should be 5