CODEWITHSUNDEEP
pythonsqlalchemyormflask-sqlalchemy
Unlik
Unlik

Reputation: 13

Subquery to the same table in SQLAlchemy ORM

Hello SQLAlchemy masters, I am just facing a problem with how to use SQLAlchemy ORM in python for the SQL query

SELECT systems.name,
       (
       SELECT date 
       FROM accounting A
       WHERE A.ticker = C.ticker AND A.info = 'Trade_opened'
       ) AS entry,
 C.*
 FROM accounting C
 JOIN systems ON C.system_id = systems.id
 WHERE C.status = 'open'

And I can't use an aliased() in a right way:

    H = aliased(Accounting, name='H')
    C = aliased(Accounting, name='C')

    his = db.session.query(H.date) \
                    .filter(H.ticker == C.ticker, H.info == r'Trade_opened')
    sql = db.session.query(Systems.name, C, his) \
                    .join(Systems, C.system_id == Systems.id) \
                    .filter(C.status == r'Open') \
                    .statement

    print(sql)

Can you help me, please?

Upvotes: 1

Views: 1129

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9049

I think you need:

  • scalar_subquery to be able to use the subquery as a column
  • select_from to be able to set the "left" side of the joins to be different from the first column (ie. C instead of systems).

I didn't test this with actual data so I don't know if it works correctly. It helps if you post your schema and some test data. I used Account because it has an easy plural, accounts, to setup a test.


Base = declarative_base()

class Account(Base):
    __tablename__ = 'accounts'
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    ticker = Column(String(length=200))
    info = Column(String(length=200))
    status = Column(String(length=200))
    system = relationship('System', backref='accounts')
    system_id = Column(Integer, ForeignKey('systems.id'))

class System(Base):
    __tablename__ = 'systems'
    id = Column(Integer, primary_key=True)
    name = Column(String(length=200))


with Session(engine) as session:
    C = aliased(Account, name='C')
    A = aliased(Account, name='A')
    date_subq = session.query(A.date).filter(and_(A.ticker == C.ticker, A.info == 'Trade_opened')).scalar_subquery()
    q = session.query(System.name, date_subq.label('entry'), C).select_from(C).join(C.system).filter(C.status == 'open')
    print (q)

Formatted SQL:

SELECT 
systems.name AS systems_name,
(SELECT "A".date 
FROM accounts AS "A" WHERE "A".ticker = "C".ticker AND "A".info = %(info_1)s) AS entry,
"C".id AS "C_id",
"C".date AS "C_date",
"C".ticker AS "C_ticker",
"C".info AS "C_info", 
"C".status AS "C_status",
"C".system_id AS "C_system_id" 
FROM accounts AS "C" 
JOIN systems ON systems.id = "C".system_id 
WHERE "C".status = %(status_1)s

Upvotes: 1

Related Questions