I've converted your fragments into a full MCVE and it runs fine, no error is generated. would need to see a stack trace. Try out the program below also.
import datetime from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import Date from sqlalchemy import DateTime from sqlalchemy import ForeignKey from sqlalchemy import func from sqlalchemy import Integer from sqlalchemy import select from sqlalchemy import String from sqlalchemy.orm import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.orm import Session Base = declarative_base() class User(Base): __tablename__ = "Users" id = Column(Integer, primary_key=True) username = Column(String) password_hash = Column(String) entries = relationship("Entry") daily_expected_calories = relationship("DailyExpectedCalories") class Entry(Base): __tablename__ = "Entries" id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("Users.id")) timestamp = Column(DateTime(timezone=True)) time_zone_utc_offset = Column(Integer) calorie_count = Column(Integer) meal = Column(String) class DailyExpectedCalories(Base): __tablename__ = "DailyExpectedCalories" date = Column(Date, primary_key=True) user_id = Column(Integer, ForeignKey("Users.id"), primary_key=True) expected_Calories = Column(Integer) query_calorie_sum_less_than_expected = ( select( Entry.user_id, ( func.sum(Entry.calorie_count) < DailyExpectedCalories.expected_Calories ).label("daily_calorie_sum_less_than_expected"), ) .join( DailyExpectedCalories, Entry.user_id == DailyExpectedCalories.user_id ) .subquery() ) query = ( select(Entry) .outerjoin( query_calorie_sum_less_than_expected, Entry.user_id == query_calorie_sum_less_than_expected.c.user_id, ) .where(Entry.user_id == User.id) ) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) with s.no_autoflush: s.add_all( [ User( username="u1", entries=[Entry()], daily_expected_calories=[ DailyExpectedCalories( date=datetime.date.today(), expected_Calories=2000 ) ], ) ] ) s.commit() for entry in s.scalars(query): print(entry) On Sat, Jul 9, 2022, at 4:12 PM, Montana Burr wrote: > Hi folks! > > I have a rather complicated SQL query to perform. I kind of know how I > would do it in SQL and am looking to port it to SQLAlchemy. > > I have these ORM classes: > > class User(Base): > __tablename__ = "Users" > id = Column(Integer, primary_key=True) > username = Column(String) > password_hash = Column(String) > entries = relationship("Entry") > daily_expected_calories = relationship("DailyExpectedCalories") > > class Entry(Base): > __tablename__ = "Entries" > id = Column(Integer, primary_key=True) > user_id = Column(Integer, ForeignKey("Users.id")) > timestamp = Column(DateTime(timezone=True)) > time_zone_utc_offset = Column(Integer) > calorie_count = Column(Integer) > meal = Column(String) > > > class DailyExpectedCalories(Base): > __tablename__ = "DailyExpectedCalories" > date = Column(Date, primary_key=True) > user_id = Column(Integer, ForeignKey("Users.id"), primary_key=True) > expected_Calories = Column(Integer) > > and I am looking to build a query that gives me objects of type Entry and a > Boolean field indicating whether the user has consumed fewer calories than > expected during the day of the Entry. > > For example, if I a 1111-Calorie food in the morning, a 2222-Calorie food in > the afternoon, and a 3333-Calorie food in the afternoon, but I only expected > to eat 3000 Calories, the results of the query might look something like this: > > 07/09/2022 9:00 AM 1111 False > 07/09/2022 1:00 PM 2222 False > 07/09/2022 7:00 PM 3333 False > > I've come up with these queries: > > query_calorie_sum_less_than_expected = select(Entry.user_id, ( > func.sum(Entry.calorie_count) < > DailyExpectedCalories.expected_Calories > ).label("daily_calorie_sum_less_than_expected")).join( > DailyExpectedCalories, > Entry.user_id == DailyExpectedCalories.user_id).subquery() > query = select(Entry).outerjoin( > query_calorie_sum_less_than_expected, > Entry.user_id == > query_calorie_sum_less_than_expected.c.user_id).where( > Entry.user_id == user.id) > > but when I do > > results = engine.execute(query) > > and then do something like > > for row in results: > results.daily_calorie_sum_less_than_expected > > SQLAlchemy complains that the aforementioned column does not exist. > > So how would I do the kind of query I'm looking to do? > > > -- > 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/4b724e77-da6c-4581-aecb-f9196e53ab80n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/4b724e77-da6c-4581-aecb-f9196e53ab80n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/ef40b1ad-a7ab-4c47-8357-d3aede4dbcbd%40www.fastmail.com.