I'm trying to implement closure tables based on this blog post, which features the only complete and functional implementation of closure tables with sqlalchemy, and which I link here just to give credit, but I want to replace the children_table (see code example below) with a depth-column inside the closure_table.
The problem is that I really don't know how to set any value to this new column, since the predecessor and successor columns get set automatically via the relationship specified inside the Category-class, and I can't think of meaningful search engine keywords to find clues in the internet. I tried to find the solution in the sqlalchemy docs for quite some time now, but it is hard to recognize the relevant parts that apply to this specific task.
It would be fantastic if some sqlalchemy-specialist could teach me which parts in the code must be changed which way or at least hint me to the right chapter in the docs so that I can continue trying to figure it out myself with a sharper focus.
To clarify what I'd like to know: There are answers like this one which show how to calculate and set the depth-value in SQL:
INSERT INTO closure_tree_path (ancestor, descendant, depth)
SELECT ancestor, '{$node_id}', depth+1 FROM closure_tree_path
WHERE descendant = '{$parent_id}'
UNION ALL SELECT '{$node_id}', '{$node_id}', 0;
but the most important question is how my code must be modified to enable setting values to the depth-column in the first place - working towards the goal of a fully functional depth-column. If someone came up with the full solution, I definitely wouldn't mind, though... :-)
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base()
children_table = Table('edges', Base.metadata,
Column('predecessor', Integer,
ForeignKey('categories.id'), primary_key=True),
Column('successor', Integer,
ForeignKey('categories.id'), primary_key=True))
closure_table = Table('paths', Base.metadata,
Column('predecessor', Integer,
ForeignKey('categories.id'), primary_key=True),
Column('successor', Integer,
ForeignKey('categories.id'), primary_key=True),
Column('depth', Integer))
class Category(Base):
__tablename__ = 'categories'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False, unique=True)
children = relationship('Category', backref='predecessors',
secondary=children_table,
primaryjoin=id == children_table.c.predecessor,
secondaryjoin=id == children_table.c.successor)
descendants = relationship('Category', backref='after',
secondary=closure_table,
primaryjoin=id == closure_table.c.predecessor,
secondaryjoin=id == closure_table.c.successor)
def __init__(self, name):
self.name = name
def __repr__(self):
return '%r, %r, %r, %r' % (self.id, self.name, [x.id for x in self.descendants], [x.id for x in self.children])
def add_successor(self, other):
if other in self.children:
return
self.children.append(other)
self.descendants.append(other)
for descendent in other.descendants:
if descendent not in self.descendants:
self.descendants.append(descendent)
for ancestor in self.after:
if ancestor not in other.after:
other.after.append(ancestor)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sess = Session()
# add test data
a = Category("A")
b = Category("B")
c = Category("C")
d = Category("D")
e = Category("E")
f = Category("F")
g = Category("G")
h = Category("H")
a.add_successor(b)
a.add_successor(c)
b.add_successor(d)
b.add_successor(e)
c.add_successor(f)
c.add_successor(g)
f.add_successor(h)
sess.add(a)
print("Category Table:")
for c in sess.query(Category).all(): print(c)
print("\nChildren Table:")
for c in sess.query(children_table).all(): print(c)
print("\nClosure Table:")
for c in sess.query(closure_table).all(): print(c)
The closure_table in my running example already contains an additional column depth, but as of now it only contains None:
Category Table:
1, 'A', [2, 5, 3, 4, 6, 8, 7], [2, 5]
2, 'B', [3, 4], [3, 4]
3, 'D', [], []
4, 'E', [], []
5, 'C', [6, 8, 7], [6, 8]
6, 'F', [7], [7]
7, 'H', [], []
8, 'G', [], []
Children Table:
(5, 6)
(1, 2)
(5, 8)
(1, 5)
(6, 7)
(2, 3)
(2, 4)
Closure Table:
(5, 6, None)
(1, 6, None)
(1, 2, None)
(5, 8, None)
(1, 8, None)
(1, 5, None)
(6, 7, None)
(5, 7, None)
(1, 7, None)
(2, 3, None)
(1, 3, None)
(2, 4, None)
(1, 4, None)
However, the desired output would be:
Category Table:
1, 'A', [2, 5, 3, 4, 6, 8, 7], [2, 5]
2, 'B', [3, 4], [3, 4]
3, 'D', [], []
4, 'E', [], []
5, 'C', [6, 8, 7], [6, 8]
6, 'F', [7], [7]
7, 'H', [], []
8, 'G', [], []
Closure Table:
(5, 6, 1)
(1, 6, 2)
(1, 2, 1)
(5, 8, 1)
(1, 8, 2)
(1, 5, 1)
(6, 7, 1)
(5, 7, 2)
(1, 7, 3)
(2, 3, 1)
(1, 3, 2)
(2, 4, 1)
(1, 4, 2)
(I removed the output for the children table because the children table would get removed from the code entirely since it would not be needed anymore.)
The corresponding graph looks like this:
Bold arrows visualize the children table, dashed arrows depict the closure table.
