On Thu, Jan 7, 2010 at 11:29 AM, Eric Lemoine
<eric.lemo...@camptocamp.com> wrote:
> Hi
>
> I'm having a problem with a query where the FROM clause doesn't
> include a table that is actually required for the query.
>
> Here's my code:
>
> ----------
> subq = subquery(None,
> columns=[communes.c.nom.label("commune")],
>
> whereclause=and_(fiches_aggregees.c.id_zonage== zonages.c.gid,
> zonages.c.gid
> ==croisements.c.gid_zonage,
>
> croisements.c.gid_commune == communes.c.gid),
> distinct=True)
>
> query = select([suq.c.commune,
> func.count('*').label('nbfiches')),
>
> func.sum(fiches_aggregees.c.nbtaxons).label('nbtaxons')].group_by(subq.c.commune)
>
> meta.Session.execute(query).fetchall()
> ----------
>
> Here's the query as seen in the postgres logs:
>
> ----------
> SELECT anon_1.commune, count(E'*') AS nbfiches,
> sum(fiches_agregees.nbtaxons) AS nbtaxons
> FROM
> (SELECT DISTINCT communes.nom AS commune
> FROM communes, zonages, croisements
> WHERE fiches_agregees.id_zonage = zonages.gid
> AND zonages.gid = croisements.gid_zonage
> AND croisements.gid_commune = communes.gid) AS anon_1,
> fiches_agregees
> GROUP BY anon_1.commune
> ----------
>
> The query fails because the "fiches_agregees" table isn't in the FROM
> clause of the subquery. I can't figure out why it's not, given there's
> a reference to it in the WHERE clause.
Got it:
subq = subquery(..., correlate=False)
Thanks,
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.com
--
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.
