I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example:
outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. (That example was really a useless scenario, I'll try to make a better one later) 2011/5/26 Michael Bayer <mike...@zzzcomputing.com> > 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. > -- 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.
