SQLAlchemy expression language: how to join table with subquery?
<p>I have a subquery table <code>inner_stmt</code>, which I want to join with a table <code>revisions</code>. But <code>revisions.join()</code> gives the following error:</p>

<pre><code>Neither 'Label' object nor 'Comparator' object has an attribute 'c'
</code></pre>

<p>Here is my code. What am I doing wrong?</p>

<pre><code>inner_stmt = select([
        ratings.c.article_name,
        func.min(ratings.c.timestamp).label('mintime')]) \
    .group_by(ratings.c.article_name).label('firstga')

stmt = select([
        revisions.c.article_id,
        func.max(revisions.c.length_bytes)]) \
    .select_from(revisions.join(
        inner_stmt,
        revisions.c.article_name == inner_stmt.c.article_name)) \
    .group_by(table.c.article_id)        
</code></pre>

<p>You are <a href="http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.SelectBase.label" rel="nofollow"><code>label</code></a>ing your subquery <code>inner_stmt</code>. <code>label</code> is for columns or expressions, i.e. <code>SELECT ... AS ...</code>. You want <a href="http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.alias" rel="nofollow"><code>alias</code></a> instead, which is for subquery expressions, i.e. <code>FROM ... AS ...</code>. You cannot access columns (<code>.c.&lt;name&gt;</code>) from the former, even in SQL, because it's a SQL expression.</p>

python mysql sqlalchemy