changing it more the way you had
lineage_nodes = recursive_cte.union_all(
select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id])
.join(
recursive_cte, recursive_cte.c.selfreferencing_staff_id == s.c.id
)
.filter(recursive_cte.c._enabling_factor == None)
)
i applied an alias to the CTE before unioning it, please take that out and try
again. CTE inside the subquery can't happen, but shoudnt be necessary.
On Sun, Sep 26, 2021, at 3:53 PM, niuji...@gmail.com wrote:
> Hi Mike,
> This code doesn't seem to yield desired result, now all the
>
Hi Mike,
This code doesn't seem to yield desired result, now all the
`effective_enabling_factor` is `None`, except for those rows that carries a
`_enabling_factor` on itself. In other words, it doesn't seem to recusively
search the next immediately parent's marker at all.
And the SQL it
OK there's various small issues here but overall SQLAlchemy's new "nesting"
feature does not seem to work correctly yet for a RECURSIVE CTE with UNION,
meaning it's not very useful for RECURSIVE right now.
But here's the thing, I'm not as SQL expert as some people think but I don't
think it
Hi Mike,
Yes the SQL code runs as desired, but the Python code doesn't,
unfortunately.
After reading the references you pointed out, my Python code looks like
this:
class Staff(Base):
id = Column(Integer, primary_key=True)
selfreferencing_staff_id = Column(
Integer,
well __class__.__table__ isn't going to be there inside the class body, just to
make things simple you will want to add this column_property() after the Staff
class is fully defined; then you make use of Staff. to get at columns.
Hi Mike, thanks for pointing out the direction.
I've worked out the SQL, but failed when converting to SQLAlchemy construct.
My SQL query looks like this:
SELECT id, (
WITH lineage_nodes (id, _enabling_factor, selfreferencing_staff_id)
AS
(
SELECT anchor_s.id,
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
