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/4b724e77-da6c-4581-aecb-f9196e53ab80n%40googlegroups.com.
