Or, is the query right? And if so, how do I get the
"daily_calorie_sum_less_than_expected" column?
On Saturday, July 9, 2022 at 2:12:35 PM UTC-6 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0ba2c9f1-e095-434f-b1c5-4d1de56d40dcn%40googlegroups.com.
