Labeled subqueries

[Konsta Vesterinen]

Atleast PostgreSQL requires aliases for all subqueries. I'm trying to formulate the following query into SQLAlchemy expression construct:

  SELECT count(DISTINCT :param_1) AS count_1
  FROM (
      SELECT company.id AS id
      FROM company
  ) AS company_ids

Now assume Company is any declarative class containing only one column (id).

  company_ids = db.select([Company.id](Company.id), from_obj=Company.__table__)

  print db.session.execute(db.select([db.func.count(db.distinct('id'))](db.func.count(db.distinct('id'))), from_obj=company_ids.label('company_ids')))

It gives me the following exception:

AttributeError: Neither 'Label' object nor 'Comparator' object has an attribute '_hide_froms'

I know the query I provided here makes a little sense (it could be simplified to a query without subqueries). I provided it as an example of failing scenario.

[Konsta Vesterinen]

As a follow up it would be nice to be able to use labeled unioned subqueries, for example:

  import sqlalchemy as db

  company_ids = db.select([Company.id](Company.id), from_obj=Company.__table__)

  user_ids = db.select([User.id](User.id), from_obj=User.__table__)

  print db.session.execute(db.select([db.func.count(db.distinct('id'))](db.func.count(db.distinct('id'))), from_obj=company_ids.union(user_ids).label('entity_ids'))).fetchall()

[Konsta Vesterinen]

I was able to bypass this problem by forcing the subquery to render itself as string before passing it to from_obj. This introduces other problems because then I can't use SQLAlchemy's correlated subqueries for example.

company_ids = db.select([Company.id](Company.id), from_obj=Company.__table__)

print db.session.execute(db.select([db.func.count(db.distinct('id'))](db.func.count(db.distinct('id'))), from_obj=unicode(company_ids.label('company_ids'))))

[Konsta Vesterinen]

My bad, found a way for doing this using alias construct.

from sqlalchemy.sql.expression import alias

company_ids = db.select([Company.id](Company.id), from_obj=Company.__table__)

print db.session.execute(db.select([db.func.count(db.distinct('id'))](db.func.count(db.distinct('id'))), from_obj=alias(company_ids, name='company_ids')))

[Mike Bayer]

yeah "label" is intended for column expressions. when you call label() on a select(), that turns it into a scalar select that you could embed as a subquery in the columns or where clause. for a from-based subquery, use alias. I wrote some background on column/where/from subqueries at http://docs.sqlalchemy.org/en/rel_0_8/glossary.html#term-subquery.