Subselect that references the outer select

[Israel Ben Guilherme Fonseca]

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

[Israel Ben Guilherme Fonseca]

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 <israe...@gmail.com>

[Michael Bayer]

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.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Israel Ben Guilherme Fonseca]

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 <mik...@zzzcomputing.com>

[Michael Bayer]

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.

[Israel Ben Guilherme Fonseca]

Sweet, it's working. :)

Now let's wait for the 0.8.

2011/5/26 Michael Bayer <mik...@zzzcomputing.com>