take a look at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries and note that when a subquery is used as a FROM clause, it acts like a table. Use the .c. attribute.
On May 26, 2011, at 3:34 PM, Israel Ben Guilherme Fonseca wrote: > I did a bit more of digging in the docs and found the 'select_from' method. I > thought that it would force the FROM statement to use ONLY what I pass as > argument. But it didn't. > > session.query(func.max(alias.data)).select_from(alias).filter(alias.id_curso > == Matricula.id_curso).subquery() > > It stills give-me two "matricula" in the from clause. The " == > Matricula.id_curso", is still enforcing the another "matricula" in the query. > > 2011/5/26 Israel Ben Guilherme Fonseca <israel....@gmail.com> > Hi, > > I'm trying to construct a query that have a subquery, and that subquery > references the outer query attribute. It's almost working actually: > > Intended select: > > select * from curso c join matricula m on c.id_curso = m.id_curso > where m.id_aluno = 1 > and m.data = (select max(sub.data) from matricula > sub > where sub.id_aluno = m.id_aluno) > > Query: > > alias = aliased(Matricula) > subquery = session.query(func.max(alias.data)).filter(alias.id_curso > == Matricula.id_curso).subquery() > lista = session.query(Curso) \ > .join(Matricula) \ > .filter(Matricula.id_aluno == 1) \ > .filter(Matricula.data == subquery) \ > .all() > > Result select: > > SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, > curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, > curso.nome AS curso_nome > FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso > WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT > max(matricula_1.data) AS max_1 > > FROM matricula AS matricula_1, matricula > > WHERE matricula.id_curso = matricula_1.id_curso) > > The only problem here is: the subselect is using two references to > "matricula" in the from clause: > > FROM matricula AS matricula_1, matricula <<<<< this guy shouldn't exist. > > I just want the "matricula" from the outer select. > > Fixing that, and i'm done. But how can I do that? > > Thanks in advance > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@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 sqlalchemy@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.
