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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a5e26719-3e5e-4f6e-92fc-c3319ad3ec6fn%40googlegroups.com.
