How to join to generate_series with SQLAlchemy

user_1083976 0
Viewed 1535

I'm trying to bin some events by month.

date_list = func.generate_series(start, end, '1 month')

db.session.query(func.count(Event.id)).join(date_list, true()).outerjoin(Event, Event.time==func.date(date_list)).all()

But I'm getting an error:

NotSupportedError: (psycopg2.errors.FeatureNotSupported) set-returning functions are not allowed in JOIN conditions

What's a simple example of using a series on the left side of the join with SQLAlchemy, and then using an aggregate like count?

sqlalchemy postgresql python
2 Answers

I wonder if you have used this as a reference and forgot to use FunctionElement.alias() to produce a selectable out of the generate_series() function expression. Now you are passing the set returning function expression as is on the right hand side of the ON clause predicate. Instead use column() to reference the date from the series. Controlling the left hand side of a join is done using Query.select_from():

from sqlalchemy import func, column

start = ...
end = ...
date_list = func.generate_series(start, end, '1 month').alias('month')
month = column('month')

db.session.query(month, func.count(Event.id)).\
    select_from(date_list).\
    outerjoin(Event, func.date_trunc('month', Event.time) == month).\
    group_by(month)
    all()
user_26816320

This code worked on the latest update of sqlalchemy 1.4 and async

        start = ...
        end = datetime.now()

        date_list = select(
            func.generate_series(
                start, end, timedelta(month=1)
            ).label('month')
        ).subquery()

        response = await db_session.exec(
            select(
                func.count(Event.id),                
                func.date_trunc('day', date_list.c.month)
            )
            .join(
                date_list,
                func.date_trunc('month', Event.time)
                == func.date_trunc('month', date_list.c.month),
            )
            .order_by(date_list.c.month.desc())
            .group_by(date_list.c.month)            
        )
        response = response.all()
user_125381060
Related