[sqlalchemy] Announcement: API Logic Server Docker Support

[Val Huber]

You may be interested in ApiLogicServer 
, open source on Git (79k 
downloads to date).

With 1 command, it creates a customizable project providing a JSON:API, and a 
Basic Web App.  These are based on SQLAlchemy, so it uses sqlacodegen to create 
your model classes.

SQLAlchemy updates (whether by API, Web App or Python) are monitored by 
spreadsheet-like rules 
 - 40X 
more concise than code, extensible with Python.

Release 3.01.10 includes Docker support:
It avoids the often fiddly Python install 

It enables vscode (without Python install), providing code completion to build 
rules, rule debugging, etc

You can also get prepared several DB images (MySQL, Postgresql, and SQLServer ) 
- super for testing

Regards,
Val

-- 
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/D4FB9A2F-388F-4B00-B2A1-2DE5591E37C1%40gmail.com.

Re: [sqlalchemy] recursive cte use current row as anchor

[Mike Bayer]

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)
)

produces:

WITH RECURSIVE lineage_nodes(anchor_id, _enabling_factor, 
selfreferencing_staff_id) AS 
(SELECT anchor_s.id AS anchor_id, anchor_s._enabling_factor AS 
_enabling_factor, anchor_s.selfreferencing_staff_id AS selfreferencing_staff_id
FROM staff AS anchor_s UNION ALL SELECT s.id AS id, s._enabling_factor AS 
_enabling_factor, s.selfreferencing_staff_id AS selfreferencing_staff_id
FROM staff AS s JOIN lineage_nodes ON lineage_nodes.selfreferencing_staff_id = 
s.id
WHERE lineage_nodes._enabling_factor IS NULL)
SELECT staff.id AS staff_id, staff.selfreferencing_staff_id AS 
staff_selfreferencing_staff_id, staff._enabling_factor AS 
staff__enabling_factor, staff.record_id AS staff_record_id, (SELECT 
final_s._enabling_factor
FROM staff AS final_s
WHERE final_s.record_id = (SELECT min(lineage_nodes.anchor_id) AS min_1
FROM lineage_nodes
WHERE lineage_nodes.anchor_id = staff.id)) AS anon_1
FROM staff

so the UNION joins out to "lineage_nodes" again.  I've not really worked with 
recursive CTEs much but i think that's the idea.



On Sun, Sep 26, 2021, at 4:01 PM, Mike Bayer wrote:
> 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 
>> `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 generates is a little confusing, it seems to me that we 
>> introduce a `recursive_cte_a` aliased table and gain nothing from it:
>> 
>> WITH RECURSIVE lineage_nodes(
>>   anchor_id, _enabling_factor, selfreferencing_staff_id
>> ) AS (
>>   SELECT 
>> anchor_f.id AS anchor_id, 
>> anchor_f._enabling_factor AS _enabling_factor, 
>> anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
>>   FROM 
>> staff AS anchor_f 
>>   UNION ALL 
>>   SELECT 
>> anchor_f.id AS id, 
>> anchor_f._enabling_factor AS _enabling_factor, 
>> anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
>>   FROM 
>> staff AS anchor_f 
>> JOIN lineage_nodes AS anon_2 ON anon_2.selfreferencing_staff_id = 
>> anchor_f.id 
>>   WHERE 
>> anon_2._enabling_factor IS NULL
>> ) 
>> SELECT 
>>   staff.id AS staff_id, 
>>   staff._enabling_factor AS staff__enabling_factor, 
>>   (
>> SELECT 
>>   final_f._enabling_factor 
>> FROM 
>>   staff AS final_f 
>> WHERE 
>>   final_f.id = (
>> SELECT 
>>   min(lineage_nodes.anchor_id) AS min_1 
>> FROM 
>>   lineage_nodes 
>> WHERE 
>>   lineage_nodes.anchor_id = staff.id
>>   )
>>   ) AS anon_1 
>> FROM 
>>   staff
>> 
>> 
>> On Sunday, September 26, 2021 at 7:35:20 AM UTC-7 Mike Bayer wrote:
>>> __
>>> 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 should be necessary for a CTE to actually be embedded in a 
>>> subquery.  You can keep the CTE on top, where it can more efficiently 
>>> create a set of all rows, and then refer to it within a normal correlated 
>>> subquery that's in the column_property().
>>> 
>>> ive written the below MCVE we can work with going forward.  see how close 
>>> this is, but the goal is, keep the CTE on top as it normally can be and do 
>>> correlations outside of it.
>>> 
>>> from sqlalchemy import Column
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy import func
>>> from sqlalchemy import Integer
>>> from sqlalchemy import select
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import backref
>>> from sqlalchemy.orm import column_property
>>> from sqlalchemy.orm import relationship
>>> from sqlalchemy.orm import Session
>>> 
>>> Base = declarative_base()
>>> 
>>> 
>>> class Staff(Base):
>>> __tablename__ = "staff"
>>> 
>>> id = Column(Integer, primary_key=True)
>>> 
>>> selfreferencing_staff_id = Column(
>>> Integer,
>>> ForeignKey("staff.id", onupdate="CASCADE", ondelete="SET NULL"),
>>> )
>>> _enabling_factor = Column(Integer)
>>> record_id = Column(Integer)
>>> 
>>> team_members = relationship(
>>> "Staff", backref=backref("supervisor", remote_side="Staff.id")
>>> )
>>> 
>>> 
>>> 

Re: [sqlalchemy] recursive cte use current row as anchor

[Mike Bayer]

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 
> `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 generates is a little confusing, it seems to me that we 
> introduce a `recursive_cte_a` aliased table and gain nothing from it:
> 
> WITH RECURSIVE lineage_nodes(
>   anchor_id, _enabling_factor, selfreferencing_staff_id
> ) AS (
>   SELECT 
> anchor_f.id AS anchor_id, 
> anchor_f._enabling_factor AS _enabling_factor, 
> anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
>   FROM 
> staff AS anchor_f 
>   UNION ALL 
>   SELECT 
> anchor_f.id AS id, 
> anchor_f._enabling_factor AS _enabling_factor, 
> anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
>   FROM 
> staff AS anchor_f 
> JOIN lineage_nodes AS anon_2 ON anon_2.selfreferencing_staff_id = 
> anchor_f.id 
>   WHERE 
> anon_2._enabling_factor IS NULL
> ) 
> SELECT 
>   staff.id AS staff_id, 
>   staff._enabling_factor AS staff__enabling_factor, 
>   (
> SELECT 
>   final_f._enabling_factor 
> FROM 
>   staff AS final_f 
> WHERE 
>   final_f.id = (
> SELECT 
>   min(lineage_nodes.anchor_id) AS min_1 
> FROM 
>   lineage_nodes 
> WHERE 
>   lineage_nodes.anchor_id = staff.id
>   )
>   ) AS anon_1 
> FROM 
>   staff
> 
> 
> On Sunday, September 26, 2021 at 7:35:20 AM UTC-7 Mike Bayer wrote:
>> __
>> 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 should be necessary for a CTE to actually be embedded in a 
>> subquery.  You can keep the CTE on top, where it can more efficiently create 
>> a set of all rows, and then refer to it within a normal correlated subquery 
>> that's in the column_property().
>> 
>> ive written the below MCVE we can work with going forward.  see how close 
>> this is, but the goal is, keep the CTE on top as it normally can be and do 
>> correlations outside of it.
>> 
>> from sqlalchemy import Column
>> from sqlalchemy import ForeignKey
>> from sqlalchemy import func
>> from sqlalchemy import Integer
>> from sqlalchemy import select
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import backref
>> from sqlalchemy.orm import column_property
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.orm import Session
>> 
>> Base = declarative_base()
>> 
>> 
>> class Staff(Base):
>> __tablename__ = "staff"
>> 
>> id = Column(Integer, primary_key=True)
>> 
>> selfreferencing_staff_id = Column(
>> Integer,
>> ForeignKey("staff.id", onupdate="CASCADE", ondelete="SET NULL"),
>> )
>> _enabling_factor = Column(Integer)
>> record_id = Column(Integer)
>> 
>> team_members = relationship(
>> "Staff", backref=backref("supervisor", remote_side="Staff.id")
>> )
>> 
>> 
>> anchor_s = Staff.__table__.alias(name="anchor_s")
>> s = Staff.__table__.alias(name="s")
>> final_s = Staff.__table__.alias(name="final_s")
>> 
>> recursive_cte = (
>> select(
>> [
>> anchor_s.c.id.label("anchor_id"),
>> anchor_s.c._enabling_factor,
>> anchor_s.c.selfreferencing_staff_id,
>> ]
>> ).select_from(anchor_s)
>> # this part we are going to move to the outside
>> # .where(anchor_s.c.id == Staff.id)
>> .cte(name="lineage_nodes", recursive=True)
>> )
>> 
>> recursive_cte_a = recursive_cte.alias()
>> 
>> lineage_nodes = recursive_cte.union_all(
>> select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id])
>> .join(
>> recursive_cte_a, recursive_cte_a.c.selfreferencing_staff_id == s.c.id
>> )
>> .filter(recursive_cte_a.c._enabling_factor == None)
>> )
>> 
>> top_id = (
>> select(func.min(lineage_nodes.c.anchor_id))
>> # here's where we moved it
>> .where(lineage_nodes.c.anchor_id == Staff.id)
>> .correlate(Staff)
>> .scalar_subquery()
>> )
>> 
>> 
>> Staff.effective_enabling_factor = column_property(
>> select(final_s.c._enabling_factor)
>> .where(final_s.c.record_id == top_id)
>> .scalar_subquery()
>> )  # This is where I define the desired column_property.
>> 
>> 
>> s = Session()
>> 
>> q = s.query(Staff)
>> print(q)
>> 
>> On Sun, Sep 26, 2021, at 2:56 AM, niuji...@gmail.com wrote:
>>> 
>>> Hi Mike,
>>> 
>>> Yes the SQL code runs as 

Re: [sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

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 generates is a little confusing, it seems to me that we 
introduce a `recursive_cte_a` aliased table and gain nothing from it:

WITH RECURSIVE lineage_nodes(
  anchor_id, _enabling_factor, selfreferencing_staff_id
) AS (
  SELECT 
anchor_f.id AS anchor_id, 
anchor_f._enabling_factor AS _enabling_factor, 
anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
  FROM 
staff AS anchor_f 
  UNION ALL 
  SELECT 
anchor_f.id AS id, 
anchor_f._enabling_factor AS _enabling_factor, 
anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
  FROM 
staff AS anchor_f 
JOIN lineage_nodes AS anon_2 ON anon_2.selfreferencing_staff_id = 
anchor_f.id 
  WHERE 
anon_2._enabling_factor IS NULL
) 
SELECT 
  staff.id AS staff_id, 
  staff._enabling_factor AS staff__enabling_factor, 
  (
SELECT 
  final_f._enabling_factor 
FROM 
  staff AS final_f 
WHERE 
  final_f.id = (
SELECT 
  min(lineage_nodes.anchor_id) AS min_1 
FROM 
  lineage_nodes 
WHERE 
  lineage_nodes.anchor_id = staff.id
  )
  ) AS anon_1 
FROM 
  staff


On Sunday, September 26, 2021 at 7:35:20 AM UTC-7 Mike Bayer wrote:

> 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 should be necessary for a CTE to actually be embedded in a 
> subquery.  You can keep the CTE on top, where it can more efficiently 
> create a set of all rows, and then refer to it within a normal correlated 
> subquery that's in the column_property().
>
> ive written the below MCVE we can work with going forward.  see how close 
> this is, but the goal is, keep the CTE on top as it normally can be and do 
> correlations outside of it.
>
> from sqlalchemy import Column
> from sqlalchemy import ForeignKey
> from sqlalchemy import func
> from sqlalchemy import Integer
> from sqlalchemy import select
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import backref
> from sqlalchemy.orm import column_property
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Staff(Base):
> __tablename__ = "staff"
>
> id = Column(Integer, primary_key=True)
>
> selfreferencing_staff_id = Column(
> Integer,
> ForeignKey("staff.id", onupdate="CASCADE", ondelete="SET NULL"),
> )
> _enabling_factor = Column(Integer)
> record_id = Column(Integer)
>
> team_members = relationship(
> "Staff", backref=backref("supervisor", remote_side="Staff.id")
> )
>
>
> anchor_s = Staff.__table__.alias(name="anchor_s")
> s = Staff.__table__.alias(name="s")
> final_s = Staff.__table__.alias(name="final_s")
>
> recursive_cte = (
> select(
> [
> anchor_s.c.id.label("anchor_id"),
> anchor_s.c._enabling_factor,
> anchor_s.c.selfreferencing_staff_id,
> ]
> ).select_from(anchor_s)
> # this part we are going to move to the outside
> # .where(anchor_s.c.id == Staff.id)
> .cte(name="lineage_nodes", recursive=True)
> )
>
> recursive_cte_a = recursive_cte.alias()
>
> lineage_nodes = recursive_cte.union_all(
> select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id])
> .join(
> recursive_cte_a, recursive_cte_a.c.selfreferencing_staff_id == 
> s.c.id
> )
> .filter(recursive_cte_a.c._enabling_factor == None)
> )
>
> top_id = (
> select(func.min(lineage_nodes.c.anchor_id))
> # here's where we moved it
> .where(lineage_nodes.c.anchor_id == Staff.id)
> .correlate(Staff)
> .scalar_subquery()
> )
>
>
> Staff.effective_enabling_factor = column_property(
> select(final_s.c._enabling_factor)
> .where(final_s.c.record_id == top_id)
> .scalar_subquery()
> )  # This is where I define the desired column_property.
>
>
> s = Session()
>
> q = s.query(Staff)
> print(q)
>
> On Sun, Sep 26, 2021, at 2:56 AM, niuji...@gmail.com wrote:
>
>
> 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,
> ForeignKey('staff_table.id',
>onupdate="CASCADE",
>ondelete='SET NULL'))
>   _enabling_factor = Column(Integer)
>  

Re: [sqlalchemy] recursive cte use current row as anchor

[Mike Bayer]

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 should be necessary for a CTE to actually be embedded in a subquery.  
You can keep the CTE on top, where it can more efficiently create a set of all 
rows, and then refer to it within a normal correlated subquery that's in the 
column_property().

ive written the below MCVE we can work with going forward.  see how close this 
is, but the goal is, keep the CTE on top as it normally can be and do 
correlations outside of it.

from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref
from sqlalchemy.orm import column_property
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class Staff(Base):
__tablename__ = "staff"

id = Column(Integer, primary_key=True)

selfreferencing_staff_id = Column(
Integer,
ForeignKey("staff.id", onupdate="CASCADE", ondelete="SET NULL"),
)
_enabling_factor = Column(Integer)
record_id = Column(Integer)

team_members = relationship(
"Staff", backref=backref("supervisor", remote_side="Staff.id")
)


anchor_s = Staff.__table__.alias(name="anchor_s")
s = Staff.__table__.alias(name="s")
final_s = Staff.__table__.alias(name="final_s")

recursive_cte = (
select(
[
anchor_s.c.id.label("anchor_id"),
anchor_s.c._enabling_factor,
anchor_s.c.selfreferencing_staff_id,
]
).select_from(anchor_s)
# this part we are going to move to the outside
# .where(anchor_s.c.id == Staff.id)
.cte(name="lineage_nodes", recursive=True)
)

recursive_cte_a = recursive_cte.alias()

lineage_nodes = recursive_cte.union_all(
select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id])
.join(
recursive_cte_a, recursive_cte_a.c.selfreferencing_staff_id == s.c.id
)
.filter(recursive_cte_a.c._enabling_factor == None)
)

top_id = (
select(func.min(lineage_nodes.c.anchor_id))
# here's where we moved it
.where(lineage_nodes.c.anchor_id == Staff.id)
.correlate(Staff)
.scalar_subquery()
)


Staff.effective_enabling_factor = column_property(
select(final_s.c._enabling_factor)
.where(final_s.c.record_id == top_id)
.scalar_subquery()
)  # This is where I define the desired column_property.


s = Session()

q = s.query(Staff)
print(q)

On Sun, Sep 26, 2021, at 2:56 AM, niuji...@gmail.com wrote:
> 
> 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,
> ForeignKey('staff_table.id',
>onupdate="CASCADE",
>ondelete='SET NULL'))
>   _enabling_factor = Column(Integer)
>  
>team_members = relationship('Staff',
>backref=backref(
>'supervisor',
>remote_side='Staff.id'))
>
> anchor_s = Staff.__table__.alias(name="anchor_s")
> s = Staff.__table__.alias(name="s")
> final_s = Staff.__table__.alias(name="final_s")
> 
> recursive_cte = select([
> anchor_s.c.id, anchor_s.c._enabling_factor, 
> anchor_s.c.selfreferencing_staff_id
>]).select_from(anchor_s).where(anchor_s.c.id == Staff.id).cte(
> name="lineage_nodes", recursive=True)
> 
> 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))
> 
> top_id = select(sasql.func.min(lineage_nodes.c.id))
> Staff.effective_enabling_factor = 
> column_property(select(final_s.c._enabling_factor).where(
> final_s.c.record_id == top_id).scalar_subquery())  # This is where I 
> define the desired column_property.
> 
> 
> 
> 
> The problems seems to be that this code doesn't generate `correlated 
> subquery` for each `Staff` row ( as in the SQL code `WHERE anchor_s.id = 
> outer_s.id`). Could you take a look?
> On Saturday, September 25, 2021 at 8:26:46 PM UTC-7 Mike Bayer wrote:
>> __
>> 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 

Re: [sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

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,
ForeignKey('staff_table.id',
   onupdate="CASCADE",
   ondelete='SET NULL'))
  _enabling_factor = Column(Integer)
 
   team_members = relationship('Staff',
   backref=backref(
   'supervisor',
   remote_side='Staff.id'))
   
anchor_s = Staff.__table__.alias(name="anchor_s")
s = Staff.__table__.alias(name="s")
final_s = Staff.__table__.alias(name="final_s")

recursive_cte = select([
anchor_s.c.id, anchor_s.c._enabling_factor, 
anchor_s.c.selfreferencing_staff_id
   ]).select_from(anchor_s).where(anchor_s.c.id == Staff.id).cte(
name="lineage_nodes", recursive=True)

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))

top_id = select(sasql.func.min(lineage_nodes.c.id))
Staff.effective_enabling_factor = 
column_property(select(final_s.c._enabling_factor).where(
final_s.c.record_id == top_id).scalar_subquery())  # This is where 
I define the desired column_property.




The problems seems to be that this code doesn't generate `correlated 
subquery` for each `Staff` row ( as in the SQL code `WHERE anchor_s.id = 
outer_s.id`). Could you take a look?
On Saturday, September 25, 2021 at 8:26:46 PM UTC-7 Mike Bayer wrote:

> 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.  
> https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#using-column-property
>  
> will show how to assign a new column_property to the class after the fact.
>
> next, the query you have is correlating, but i really dont have any idea 
> if SQLAlchemy is going to handle a correlated column deep inside of a CTE 
> like that.   The form you have, with the CTE embedded in the parenthesis, 
> is only possible with the most recent SQLAlchemy 1.4.24 release where 
> someone contributed a new parameter called "nesting", which means the CTE 
> should not be moved to the top level of the SELECT.   still, not really 
> sure what a CTE will do inside a correlated subquery like that.   For an 
> example of how to use "nesting" see example four at 
> https://docs.sqlalchemy.org/en/14/core/selectable.html?highlight=hascte#sqlalchemy.sql.expression.HasCTE.cte
>  
> .  for the correlate, when you write out the select() that refers to 
> "outer_s", add .correlate(Staff) to it, which means Staff isn't added to 
> the FROM list, it's assumed to be on the outside.
>
> give those a try but im not totally sure CTEs work as correlated 
> subqueries right now, it's not been tried.  I assume you've confirmed this 
> query actually runs, im surprised you can even correlate inside of a CTE 
> like that.
>
>
>
>
>
> On Sat, Sep 25, 2021, at 12:04 AM, niuji...@gmail.com wrote:
>
> 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, anchor_s._enabling_factor, 
> anchor_s.selfreferencing_staff_id
> FROM staff_table AS anchor_s
> WHERE anchor_s.id = outer_s.id
> 
> UNION ALL
> 
> SELECT s.id, s._enabling_factor, s.selfreferencing_staff_id
> FROM lineage_nodes AS l
> INNER JOIN staff_table AS s
> ON l.selfreferencing_staff_id = s.id
> WHERE l._enabling_factor IS NULL
> ),
> 
> top_node_id (top_id) AS
> (
> SELECT MIN(id) AS top_id FROM lineage_nodes
> )
> 
> SELECT staff_table._enabling_factor
> FROM staff_table
> INNER JOIN top_node_id
> ON staff_table.id = top_node_id.top_id
> ) AS effective_enabling_factor
> FROM staff_table AS outer_s;
>
>
>
>
> My Python codes looks like this:
>
>
> class Staff(Base):
>   id  = Column(Integer, primary_key=True)
>   selfreferencing_staff_id = Column(
> Integer,
> ForeignKey('staff_table.id',
>onupdate="CASCADE",
>ondelete='SET NULL'))
>