How do I access a column that is the result of an outerjoined subquery?

76 views
Skip to first unread message

Montana Burr

unread,
Jul 9, 2022, 4:12:35 PM7/9/22
to sqlalchemy
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?

Montana Burr

unread,
Jul 9, 2022, 4:13:13 PM7/9/22
to sqlalchemy
Or, is the query right? And if so, how do I get the "daily_calorie_sum_less_than_expected" column?

Mike Bayer

unread,
Jul 10, 2022, 11:27:23 AM7/10/22
to noreply-spamdigest via sqlalchemy
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)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.

Montana Burr

unread,
Jul 10, 2022, 12:20:12 PM7/10/22
to sqlalchemy
Thank you, Mike, and apologies for not providing a stack trace or MCVE of my own - I assumed the code I did provide was sufficient.

So, take your program and add

for entry in s.execute(query):
    print(entry.daily_calories_less_than_expected)

to the bottom. This represents what I'm trying to do - access an apparently nonexistent column (apologies if I didn't make that clear earlier).

I just ran your program with that change and was able to reproduce the original error, so I'm assuming that either both of our queries are wrong, or the code in the "for" loop I just mentioned is wrong.


Montana Burr

unread,
Jul 10, 2022, 12:25:34 PM7/10/22
to sqlalchemy
I suppose adding the "for" loop may be unnecessary.

Simply change print(entry) in the s.scalars loop to print(entry.daily_calories_less_than_expected) and you'll (hopefully) receive a similar error: "AttributeError: 'Entry' object has no attribute 'daily_calories_less_than_expected'"

Mike Bayer

unread,
Jul 10, 2022, 12:54:19 PM7/10/22
to noreply-spamdigest via sqlalchemy
well your final select() is only against Entry, you don't have the daily expected calories part in the list of columns you are expecting.  Also, your Entry class has no attribute called daily_calories_less_than_expected on it directly. 

there's two ways to get the data you want, one is to query for the additional column, see below

query = (
    select(
        Entry,
        query_calorie_sum_less_than_expected.c.daily_calorie_sum_less_than_expected,
    )
    .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(calorie_count=500)],
                daily_expected_calories=[
                    DailyExpectedCalories(
                        date=datetime.date.today(), expected_Calories=2000
                    )
                ],
            )
        ]
    )
s.commit()

for entry, dclte in s.execute(query):
    print(f"{entry} {dclte}")


the other is to make an attribute on Entry directly, there's actually a few ways to do that also :)  general docs are at https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html .  Here it is using query_expression() so that you can keep using the same query you have, omitted the test data this time:

# mapping 

from sqlalchemy.orm import query_expression

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)
    daily_calorie_sum_less_than_expected = query_expression()



# building up the query:

from sqlalchemy.orm import with_expression

query = (
    select(
        Entry,
    )
    .options(
        with_expression(
            Entry.daily_calorie_sum_less_than_expected,
            query_calorie_sum_less_than_expected.c.daily_calorie_sum_less_than_expected,
        )
    )
    .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)
)


# iterating:

for entry in s.scalars(query):
    print(f"{entry} {entry.daily_calorie_sum_less_than_expected}")

Montana Burr

unread,
Jul 10, 2022, 1:30:46 PM7/10/22
to sqlalchemy

I see, thank you for the assistance.

Doing stuff like this would be a LOT easier if there was an equivalent to "SELECT *"

Mike Bayer

unread,
Jul 10, 2022, 1:47:33 PM7/10/22
to noreply-spamdigest via sqlalchemy
well you can do `select('*')` if you want but if you want the rows interpreted as Entry objects and not individual column objects you need to tell that to the program.

select * at the moment works more easily with a core execution, like this:

query = (
    select('*').select_from(Entry)
    .outerjoin(...)

for row in s.connection().execute(query):
    print(f"{row}")


output

SELECT * 
FROM "Entries" LEFT OUTER JOIN (SELECT "Entries".user_id AS user_id, sum("Entries".calorie_count) < "DailyExpectedCalories"."expected_Calories" AS daily_calorie_sum_less_than_expected 
FROM "Entries" JOIN "DailyExpectedCalories" ON "Entries".user_id = "DailyExpectedCalories".user_id) AS anon_1 ON "Entries".user_id = anon_1.user_id, "Users" 
WHERE "Entries".user_id = "Users".id
2022-07-10 13:46:32,563 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ()
(1, 1, None, None, 500, None, 1, 1, 1, 'u1', None)


I can try to see if session.execute(query) where the Q is ORM enabled can still work with "select *"
Reply all
Reply to author
Forward
0 new messages