title: Joining onto a subquery in SQLAlchemy
body: <p>I have the follwing SQL query (It get's the largest of a certain column per group, with 3 things to group by):</p>
  
  <pre><code>select p1.Name, p1.nvr, p1.Arch, d1.repo, p1.Date
  from Packages as p1 inner join
      Distribution as d1
      on p1.rpm_id = d1.rpm_id inner join (
          select Name, Arch, repo, max(Date) as Date
          from Packages inner join Distribution
              on Packages.rpm_id = Distribution.rpm_id
          where Name like 'p%' and repo not like '%staging'
          group by Name, Arch, repo
      ) as sq
      on p1.Name = sq.Name and p1.Arch = sq.Arch and d1.repo = sq.repo and p1.Date = sq.Date
      order by p1.nvr
  </code></pre>
  
  <p>And I'm trying to convert it to SQLAlchemy. This is what I have so far:</p>
  
  <pre><code>p1 = aliased(Packages)
  d1 = aliased(Distribution)
  sq = session.\
          query(
              Packages.Name,
              Packages.Arch,
              Distribution.repo,
              func.max(Packages.Date).\
                                          label('Date')).\
          select_from(
              Packages).\
          join(
              Distribution).\
          filter(
              queryfilter).\
          filter(
              not_(Distribution.repo.\
                                      like('%staging'))).\
          group_by(
              Packages.Name,
              Packages.Arch,
              Distribution.repo).subquery()
  
  result = session.\
              query(
                  p1, d1.repo).\
              select_from(
                  p1).\
              join(
                  d1).\
              join(
                  sq,
                  p1.Name==sq.c.Name,
                  p1.Arch==sq.c.Arch,
                  d1.repo==sq.c.repo,
                  p1.Date==sq.c.Date).\
              order_by(p1.nvr).all()
  </code></pre>
  
  <p>The problem arises when I do the join on the subquery. I get an error that states that there is no from clause to join from. This is strange because I specify one right after the subquery in the join funciton as an argument. Any idea what I'm doing wrong? Perhaps I need to alias something and do a select_from again?</p>
  
  <p>EDIT: Exact error</p>
  
  <pre><code>Could not find a FROM clause to join from. Tried joining to SELECT "Packages"."Name", "Packages"."Arch", "Distribution".repo, max("Packages"."Date") AS "Date" FROM "Packages" JOIN "Distribution" ON "Packages".rpm_id = "Distribution".rpm_id WHERE "Packages"."Name" LIKE :Name_1 AND "Distribution".repo NOT LIKE :repo_1 GROUP BY "Packages"."Name", "Packages"."Arch", "Distribution".repo, but got: Can't find any foreign key relationships between 'Join object on %(139953254400272 Packages)s(139953254400272) and %(139953256322768 Distribution)s(139953256322768)' and '%(139953257005520 anon)s'.
  </code></pre>
  
  <p>It's trying to join, but it says it doesn't know where to make the join. Is there something wrong with my syntax? I think it's correct based on what's in the join function.</p>