title: Using function output in SQLAlchemy join clause
body: <p>I am trying to translate a fairly short bit of SQL into an sqlAlchemy ORM query. The SQL uses Postgres's <code>generate_series</code> to make a set of dates and my goal is to make a set of time series arrays categorized by one of the columns.</p>
  
  <p>The tables (simplified) are very simple:</p>
  
  <pre><code>counts:
  -----------------
  count   (Integer)
  day     (Date)
  placeID (foreign key related to places)
  
  "counts_pkey" PRIMARY KEY (day, placeID)
  
  places:
  -----------------
  id
  name   (varchar)
  </code></pre>
  
  <p>The output I'm after is a time series of counts for each place including null values when counts are not reported for a day. For example, this would correspond to a series over four days:</p>
  
  <pre><code>    array_agg    |    name
  -----------------+-------------------
   {NULL,0,7,NULL} | A Place
   {NULL,1,NULL,2} | Some other place
   {5,NULL,3,NULL} | Yet another
  </code></pre>
  
  <p>I can do this fairly easily by taking a <code>CROSS JOIN</code> on a date range and places and joining that with the counts:</p>
  
  <pre><code>SELECT array_agg(counts.count), places.name 
  FROM generate_series('2018-11-01', '2018-11-04', interval '1 days') as day 
  CROSS JOIN  places 
  LEFT OUTER JOIN counts on counts.day = day.day AND counts.PlaceID = places.id 
  GROUP BY places.name;
  </code></pre>
  
  <p>What I can't seem to figure out is how to get SQLAlchemy to do this. After a lot of digging, I found an <a href="https://groups.google.com/forum/#!topic/sqlalchemy/EV-NQX4Hdu0" rel="nofollow noreferrer">old google groups thread</a> which almost works leading to this:</p>
  
  <pre><code>date_list = select([column('generate_series')])\
  .select_from(func.generate_series(backthen, today, '1 day'))\ 
  .alias('date_list')
  
  time_series = db.session.query(Place.name, func.array_agg(Count.count))\
  .select_from(date_list)\
  .outerjoin(Count, (Count.day == date_list.c.generate_series) &amp; (Count.placeID == Place.id ))\
  .group_by(Place.name)
  </code></pre>
  
  <p>This creates a sub-select for the time series, but it produces a database error:</p>
  
  <blockquote>
    <p>There is an entry for table "places", but it cannot be referenced from this part of the query.</p>
  </blockquote>
  
  <p>So my question is: how would you do this in sqlalchemy. Also, I'm open to the idea that this is difficult because my approach with the SQL is bone-headed. </p>