I am learning ORMs with SQLAlchemy and quite a noob. On reading documentation, I was confused on following use case with ORM ctes.
I will first explain what I understand by documentation, and drive you through my confusion. Please correct me if I am completely in wrong understanding, as I feel the sqlalchemy documentation is pretty confusing.
User table:
class User(
id = Column(Integer, primary_key=True,
email_address = Column(String),
name = Column(String)
)
cte_query = (
select(User).where(User.email_address == ‘abc@gmail.com’).cte()
)
Now If we try to access only user’s id from above cte, we won’t be able to do so select(cte_query.id) # throws an error, CTE object has no attribute id
However, if we map the User ORM class to the cte query, corresponding fields will be mapped, and we’ll be able to access those.
from sqlalchemy.orm import aliased
cte_query_1 = aliased(User, cte_query)
Now, select(cte_query_1.id) will give the required result.
However, if I write a CTE where I join multiple tables, how do I extract the fields of those multiple tables as above ? As an example:
I have another table: Transactions
class Transactions(
txn_id = Column(Integer, primary_key=True),
user_id = Column(Integer),
product_id = Column(Integer)
)
I have a requirement to fetch transactions for a particular user through cte.
cte_query = (
select(User.id,User.name, Transactions.txn_id).join(Transactions, User.id == Transactions.user_id).where(User.email_address==’abc@gmail.com’
)
How do I make an alias such that I can extract values such as:
select(cte_query.id, cte_query.txn_id)
ps:
I have tried making alias by passing list, by joining. Nothing seems to work. eg: aliased([Table1, Table2]) and aliased(select(table1).join(table2)
SQLAlchemy treats CTEs and subquerys as tables, so columns must be accessed thought the columns or c attributes.
cte = sa.select(MyModel.name).cte()
q = sa.select(MyOtherModel).where(MyOtherModel.name == cte.c.name)