class Staff(Base): id = Column(Integer, primary_key=True) selfreferencing_staff_id = Column( Integer, ForeignKey('staff_table.id', onupdate="CASCADE", ondelete='SET NULL')) _enabling_factor = Column(Integer) effective_enabling_factor = column_property(...) # I have trouble in this line here. team_members = relationship('Staff', backref=backref( 'supervisor', remote_side='Staff.id'))
This is a self-referencing lineage. Each staff has one supervisor above them. Each staff has a `_enabling_factor`, which can be either a Integer, or Null. A staff's `effective_enabling_factor` is either their own `_enabling_factor` value, or their supervisor's `effective_enabling_factor` if their own is Null. This seems to be a case to use recursive CTE. I can construct the query for a certain staff member, e.g. staff #5: recursive_cte = select([Staff.id, Staff._enabling_factor, Staff.selfreferencing_staff_id]).where(Staff.id==5).cte(recursive=True) lineage_nodes = recursive_cte.union_all(select([Staff.id, Staff._enabling_factor, Staff.selfreferencing_staff_id]).join(recursive_cte, recursive_cte.c.selfreferencing_staff_id==Staff.record_id).filter(recursive_cte.c._enabling_factor == None)) marker_carrying_supervisor_id = select(sasql.func.min(lineage_nodes.c.id)).scalar_subquery() select(Staff._enabling_factor).where(Staff.id==marker_carrying_supervisor_id) However, I don't see how I can write this recursive CTE as a column_property on the `Staff` class. Instead of giving specific primary key (e.g. #5), I need to somehow reference current row as the anchor. How to solve this? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a5e26719-3e5e-4f6e-92fc-c3319ad3ec6fn%40googlegroups.com.