How to join subquery results to function results

Question

I'm trying to use SQLAlchemy to write a query like this:

SELECT
    hour,
    avg(value)
from
    generate_series('2019-10-01T00:00:00'::timestamp, '2019-10-01T23:00:00'::timestamp, '0 days 3600.000000 seconds'::interval) AS hour
left outer join
    (
        select
            *
        from
            data
        where
            parent_id=10 and
            date_time >= '2019-10-01T00:00:00'::timestamp and
            date_time < '2019-10-02T00:00:00'::timestamp
    ) "inn" on date_trunc('hour', inn.date_time) = hour
group by
    hour
order by
    hour;

My data table has 3 columns, parent_id, date_time and value.

I've spend few hours already and there's no way I can get it to work exactly like above.

The closest I've got (at least semantically it make sense) is:

series = func.generate_series(
    start_date,
    end_date,
    datetime.timedelta(hours=1)
).label('hour')

subquery = (
    session
    .query(data)
    .filter(data.parent_id == parent_id)
    .filter(data.date_time >= start_date)
    .filter(data.date_time < end_date)
    .subquery()
)

query = (
    session
    .query(
        series
    )
    .outerjoin(
        subquery,
        func.date_trunc('hour', subquery.c.reading_date_time) == series
    )
)

But its not working, it's not possible to reference "hour" column like this, the error I get is Don't know how to join to SELECT (I skip code for group by because it's not an issue here)

Answer 1

Following Ilja's tips, I got it working:

series = func.generate_series(
    start_date,
    end_date,
    datetime.timedelta(hours=1)
).alias('hour')

subquery = (
    session
    .query(data)
    .filter(data.point_id == point_id)
    .filter(data.date_time >= start_date)
    .filter(data.date_time < end_date)
    .subquery()
)

r = (
    session
    .query(
        column('hour'),
        func.avg(subquery.c.value).label('avg')
    )
    .select_from(series)
    .outerjoin(
        subquery,
        func.date_trunc('hour', subquery.c.date_time) == column('hour')
    )
    .group_by(column('hour'))
    .order_by(column('hour'))
)