[sqlalchemy] Re: Subselect that references the outer select
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.
Re: [sqlalchemy] Re: Subselect that references the outer select
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.
Re: [sqlalchemy] Re: Subselect that references the outer select
On May 26, 2011, at 5:03 PM, Israel Ben Guilherme Fonseca wrote: 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. oh then you're looking for correlation: innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).correlate(outeruser) outerselect = session.query(outeruser).filter(outeruser.id == innerselect.as_scalar()) for some reason the Query is disabling auto-correlation upon subquery(), .statement or as_scalar(). Sort of wish I had noticed that before releasing 0.7. Will add a ticket to possibly change that default for 0.8, see you in a year. -- 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.
Re: [sqlalchemy] Re: Subselect that references the outer select
Sweet, it's working. :) Now let's wait for the 0.8. 2011/5/26 Michael Bayer mike...@zzzcomputing.com On May 26, 2011, at 5:03 PM, Israel Ben Guilherme Fonseca wrote: 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. oh then you're looking for correlation: innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).correlate(outeruser) outerselect = session.query(outeruser).filter(outeruser.id == innerselect.as_scalar()) for some reason the Query is disabling auto-correlation upon subquery(), .statement or as_scalar(). Sort of wish I had noticed that before releasing 0.7. Will add a ticket to possibly change that default for 0.8, see you in a year. -- 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.
