Sql alchemy correlated subquery

Question

class Person(Base):
    __tablename__ = "stanovnik"

    id = Column(Integer, primary_key=True)
    ime = Column(String(20))
    prezime = Column(String(50))
    nadimak=Column(String(50),nullable=True)
    spol = Column(String(5))
    ratni_staz = Column(Integer)
    godine = Column(Integer)
    broj_clanova=Column(Integer,nullable=True)
    sifra_adr = Column(Integer,ForeignKey('adresa.sifra_adrese'))
    sifra_part = Column(Integer,ForeignKey('stanovnik.id'),nullable=True)
    sifra_ost = Column(Integer,ForeignKey('ostali.sifra_ostali'),nullable=True)
    adrese=relationship('Address')

class Address(Base):
    __tablename__ = "adresa"

    sifra_adrese=Column(Integer(),primary_key=True)
    naziv_adrese=Column(String(25))
    sifra_mje=Column(Integer(),ForeignKey('mjesto.sifra_mjesta'))
    mjesto=relationship('Village')

Only problem is correlated subquery in SQLAlchemy, because it's hard to any help code online and chatgpt don't work in this case.

The SQL code is

SELECT id, ime, prezime, ratni_staz 
FROM stanovnik s, adresa a1 
WHERE s.sifra_adr = a1.sifra_adrese
  AND ratni_staz > (SELECT AVG(ratni_staz) 
                    FROM stanovnik s, a2.sifra_adrese
                    WHERE s.sifra_adr = a2.sifra_adrese 
                      AND a1.sifra_adrese = a2.sifra_adrese)

It will be helpful for help to resolve this correlated subquery.

Need to resolve simple correlated subquery for these two tables in select

Answer 1

SQLAlchemy will try to "autocorrelate" matching tables between the subquery and the parent query but that doesn't work here because we only want to correlate on address. So you have to specify it with correlate. I think this is what you want but maybe I don't fully understand your goal for the subquery.

with Session(engine) as session, session.begin():
    # Subquery should be a single scalar: .scalar_subquery()
    # Correlate on Address (ie. compute average across all Persons at each address): .correlate(Address)
    subq = select(func.avg(Person.ratni_staz)).where(Person.address_id == Address.id).scalar_subquery().correlate(Address)
    q = select(Person, Address).where(Person.address_id == Address.id, Person.ratni_staz > subq)
    for person, address in session.execute(q):
        print (person, address)

The documentation for correlating scalar subqueries is here: scalar-and-correlated-subqueries