How to generate this query in sqlalchemy?

Question

I want to generate this query in sqlalchemy. The table "requiree" exists in the database. There is a subquery that generates timestamps using the generate_series function.

SELECT timesteps.timestep AS timestep, d.count AS count FROM (SELECT DATE_TRUNC('hour',date_demande) AS timestep, COUNT(id) AS count FROM demande GROUP BY timestep ) AS d RIGHT OUTER JOIN (SELECT timestep FROM generate_series('2010-01-01 00:00:00'::timestamp, '2010-01-01 23:59:59'::timestamp, '1 hour'::interval) AS timestep ) AS timesteps ON d.timestep = timesteps.timestep ORDER BY timestep; 

I tried this:

 stmt = session.query( func. generate_series( datetime.datetime(2010,1,1,0,0,0), datetime.datetime(2010,1,1,23,59,59), cast('1 hour',Interval())). label('timestep') ).subquery() print stmt q = session.query( stmt.c.timestep, func.count(Demande.id)). outerjoin((Demande, grouped==stmt.c.timestep)). group_by(stmt.c.timestep) print q 

But he complains about InvalidRequesError: could not find a FROM clause to join. I assume this is caused by a subquery.

If I try to "invert" the request, it works, but it executes the "LEFT OUTER JOIN":

 q = session.query( func.count(Demande.id), stmt.c.timestep). outerjoin((stmt, grouped==stmt.c.timestep)). group_by(stmt.c.timestep) 

Since sqlalchemy does NOT have CORRECT EMBEDDED WORK, I just want to find a way to take the subquery as the first table, and the “require” table the second. That way I can use LEFT OUTER JOIN

Answer 1

The following example should give you the key (assuming I correctly guessed that Demande is a declarative model):

 joined = stmt.outerjoin(Demande.__table__, Demande.grouped==stmt.c.timestep) q = session.query(stmt.c.timestep, func.count(Demande.id)).\ select_from(joined).\ group_by(stmt.c.timestep)