Labeled subqueries
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.
Comments (4)
-
reporter -
reporter 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'))))
-
reporter 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')))
-
repo owner - changed status to wontfix
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.
- Log in to comment
As a follow up it would be nice to be able to use labeled unioned subqueries, for example: