dates.sql

SELECT * FROM (
    SELECT generate_series('2012-11-20'::date, '2014-01-01'::date,'1 day'::interval)::date AS day) 
AS dates
LEFT JOIN (
    SELECT date_trunc('day', donations_donation.created) as day, 
           SUM(donations_donation.amount) as amount_raised 
    FROM donations_donation
    WHERE created >= '2012-11-20' AND created <= '2014-01-01' group by 1 ) t
USING(day)
ORDER BY 1;

orm.py

#This is what i have tried so far

start_date = arrow.utcnow().replace(months=-2).datetime
end_date = arrow.utcnow().datetime

gen_stmt = db.session.query(func.generate_series(start_date, end_date, cast('1 day', Interval())))
base = db.session.query('*').select_from(alias(gen_stmt.subquery(), name='day'))

>>>str(base)
'SELECT * \nFROM (SELECT generate_series(:generate_series_2, :generate_series_3, CAST(:param_1 AS DATETIME)) AS generate_series_1) AS day'

#So getting a little closer, but still not right in quite a few places. But now i try and do the sub select via the join
donation_stmt db.session.query(func.date_trunc(time_unit, Donation.created).label('day'),
                               func.sum(Donation.amount).label('amount_raised')) \
                         .group_by(literal(1))

base.outerjoin(alias(donation_stmt.subquery(), name='t'))                    

#But this produces this error
"""
InvalidRequestError: Could not find a FROM clause to join from.  Tried joining to SELECT date_trunc(:date_trunc_1, donations_donation.created) AS day, sum(donations_donation.amount) AS amount_raised
FROM donations_donation GROUP BY :param_1, but got: Can't find any foreign key relationships between 'day' and 't'.
"""

#I can't find any docs at all on USING clause use in SQA!

orm2.py

    @classmethod
    def amount_raised_by(cls, time_unit='day'):
        """return amount raised grouped by ``time_unit``

        :param time_unit: postgres date_trunc time_unit

        :returns: query object
        """
        start_date = arrow.utcnow().replace(months=-2).datetime
        end_date = arrow.utcnow().datetime
        gen_stmt = db.session.query(
            func.generate_series(start_date,
                                 end_date,
                                 cast('1 day', Interval())).label('day')
        ).subquery()

        donation_day = func.date_trunc(time_unit,
                                       Donation.created).label(time_unit)
        donation_stmt = db.session.query(donation_day,
                                         func.sum(Donation.amount).label('amount_raised')) \
                                  .group_by(donation_day).subquery()

        #.filter(cls.created.between(start_date, end_date)) \
        query = db.session.query(donation_stmt.c.amount_raised, donation_stmt.c[time_unit]) \
                  .select_from(gen_stmt) \
                  .outerjoin(donation_stmt, gen_stmt.c.day == donation_stmt.c.day) \
                  .order_by(donation_stmt.c.day)

        return query

sql2.sql

SELECT anon_1.amount_raised AS anon_1_amount_raised, anon_1.day AS anon_1_day
FROM (
    SELECT generate_series(%(generate_series_1)s, %(generate_series_2)s, CAST(%(param_1)s AS INTERVAL)) AS day) AS anon_2
LEFT OUTER JOIN (
    SELECT date_trunc(%(date_trunc_1)s, donations_donation.created) AS day, sum(donations_donation.amount) AS amount_raised
    FROM donations_donation GROUP BY date_trunc(%(date_trunc_1)s, donations_donation.created)) AS anon_1
ON anon_2.day = anon_1.day ORDER BY anon_1.day

2013-11-29 11:47:26,047 INFO sqlalchemy.engine.base.Engine
{'param_1': '1 day',
'generate_series_2': datetime.datetime(2013, 11, 29, 11, 47, 4, 680364, tzinfo=tzutc()),
'generate_series_1': datetime.datetime(2013, 9, 29, 11, 47, 4, 680106, tzinfo=tzutc()),
'date_trunc_1': 'day'}

wallop.py

        start_date = arrow.utcnow().replace(months=-2).datetime
        end_date = arrow.utcnow().datetime
        gen_stmt = db.session.query(
            func.generate_series(cast(start_date, Date()),
                                 cast(end_date, Date()),
                                 cast('1 day', Interval())).label('day')
        ).subquery()

        donation_day = func.date_trunc(time_unit,
                                       Donation.created).label(time_unit)
        donation_stmt = db.session.query(donation_day,
                                         func.sum(Donation.amount).label('amount_raised')) \
                                  .filter(cls.created.between(start_date, end_date)) \
                                  .group_by(donation_day).subquery()

        query = db.session.query(donation_stmt.c.amount_raised, donation_stmt.c[time_unit]) \
                  .select_from(gen_stmt) \
                  .join(donation_stmt, gen_stmt.c.day == donation_stmt.c.day) \
                  .order_by(donation_stmt.c.day)

        return query