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? 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.
