On Jul 27, 2010, at 12:03 PM, Jesse wrote: > I'm having a problem when using correlate in a subquery. I figured I > would throw it in here before logging a bug report incase this is just > user error. > > I have two tables with a many-to-one relationship. Estimate has a > list of EstimateLines on it. I want to select in the first > EstimateLine that does not have a blank description to use as the > description on Estimate. When getting a list of Estimates I want to > sort by the description. Here is the code I'm using: > > stmt = session.query(EstimateLine.description, > EstimateLine.estimate_id) > stmt = stmt.filter(EstimateLine.estimate_id == Estimate.id) > stmt = stmt.correlate(Estimate) > stmt = stmt.filter(EstimateLine.description != '') > stmt = stmt.order_by(EstimateLine.lineno) > stmt = stmt.limit(1) > stmt = stmt.subquery() > > query = session.query(Estimate.number, stmt.c.description) > query = query.order_by(stmt.c.description) > query.all() > > Unfortunately the query it produces does not run. It produces the > following: > SELECT estimates.number AS estimates_number, anon_1.description AS > anon_1_description > FROM estimates, (SELECT estimate_lines.description AS description, > estimate_lines.estimate_id AS estimate_id > FROM estimate_lines > WHERE estimate_lines.estimate_id = estimates.id AND > estimate_lines.description != ? ORDER BY estimate_lines.lineno > LIMIT 1 OFFSET 0) AS anon_1 ORDER BY anon_1.description > > Which gives an error of "no such column: estimates.id" (in SQLite). I > have a file for reproducing this. What I would actually like is the > subquery to be part of the SELECT statement and then order by it. > Suggestions?
The SQL is actually exactly what you asked for. SQLite doesn't like your attempt to join two selectables in the FROM clause using correlation, instead of a regular join of "Estimates" and "stmt". Correlation implies you'd like your subquery in the columns or where clause. Such a subquery can only return exactly one row for one column, and in SQLA is referred to as a "scalar" selectable. Call as_scalar() on your subquery, and use the resulting object as your column expression. There is no .c. attribute. That said, your subquery would be simpler and probably more efficient if you kept it in the FROM clause and simply joined them together without using correlation (query.filter(stmt.c.estimate_id==Estimate.id), remove the filter() from stmt). As it stands, you will need to request your subquery once in the columns clause, implying that it is issued for every row (even though a decent query planner will realize that it can "unwrap" the correlation into a plain join), and then a second time in the ORDER BY clause, unless you give it a name (using label(), since an as_scalar() is a column expression), and then name the label in the order_by(). > > Thanks, > > Jesse > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
