this is a hefty query to dig in to but column_property() subqueries have to be formed in terms of a correlated subquery. So instead of injecting a particular primary key into it, you set it to point to the Staff.id column.
correlated subqueries are not terrific performers and the construct can be a little bit clumsy in the ORM as well, however, the second example at https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#using-column-property shows the general idea. a first step to understanding might be to write out the SQL you think you want when you SELECT some Staff rows, where one of the columns in the row is the "effective_enabling_factor". that column needs to be written as a correlated subquery for it to be compatible with column_property(). On Fri, Sep 24, 2021, at 1:47 AM, niuji...@gmail.com wrote: > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/a5e26719-3e5e-4f6e-92fc-c3319ad3ec6fn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/bc23ef01-7d9f-4049-a48a-3b4b6254aaa0%40www.fastmail.com.
