Re: [Django] #14008: GeoDjango subquery fails with bad quoting

[Django]

#14008: GeoDjango subquery fails with bad quoting
-+--
  Reporter:  Matthew | Owner:  nobody
Status:  new | Milestone:
 Component:  GIS |   Version:  1.2   
Resolution:  |  Keywords:
 Stage:  Unreviewed  | Has_patch:  0 
Needs_docs:  0   |   Needs_tests:  0 
Needs_better_patch:  0   |  
-+--
Comment (by jbronn):

 What version of Django are you using?  This is very similar to #11741 &
 #12828, which were both fixed a while ago.  Using trunk, SQL that's
 generated for me is:
 {{{
 SELECT "bugs_code"."id", "bugs_code"."area_id", "bugs_code"."type",
 "bugs_code"."code" FROM "bugs_code" WHERE "bugs_code"."area_id" IN (SELECT
 U0."id" FROM "bugs_area" U0 INNER JOIN "bugs_geometry" U1 ON (U0."id" =
 U1."area_id") WHERE (ST_Contains(U1."polygon",
 ST_GeomFromEWKB(E'\\001\\001\\000\\000
 
4l\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A'::bytea))
 AND U0."generation_low_id" <= 13  AND U0."generation_high_id" >= 13 ))
 }}}

 Notice `U1` is not quoted.  Will close this as a duplicate unless I'm
 convinced otherwise.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

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

Re: [Django] #14008: GeoDjango subquery fails with bad quoting

[Django]

#14008: GeoDjango subquery fails with bad quoting
-+--
  Reporter:  Matthew | Owner:  nobody
Status:  new | Milestone:
 Component:  GIS |   Version:  1.2   
Resolution:  |  Keywords:
 Stage:  Unreviewed  | Has_patch:  0 
Needs_docs:  0   |   Needs_tests:  0 
Needs_better_patch:  0   |  
-+--
Changes (by jbronn):

  * needs_better_patch:  => 0
  * component:  Uncategorized => GIS
  * needs_tests:  => 0
  * needs_docs:  => 0

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

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

[Django] #14008: GeoDjango subquery fails with bad quoting

[Django]

#14008: GeoDjango subquery fails with bad quoting
---+
 Reporter:  Matthew|   Owner:  nobody
   Status:  new|   Milestone:
Component:  Uncategorized  | Version:  1.2   
 Keywords: |   Stage:  Unreviewed
Has_patch:  0  |  
---+
 This code (adapted from source code of http://github.com/mysociety/mapit
 ):

 {{{
 areas = Area.objects.filter(
   polygons__polygon__contains = location,
   generation_low__lte = 13,
   generation_high__gte = 13
 )
 Code.objects.filter( area__in = areas )
 }}}

 generates the following SQL (on a PostGIS database):

 {{{
 SELECT "areas_code"."id", "areas_code"."area_id", "areas_code"."type",
 "areas_code"."code"
 FROM "areas_code"
 WHERE "areas_code"."area_id" IN (
   SELECT U0."id" FROM "areas_area" U0
   INNER JOIN "areas_geometry" U1 ON (U0."id" = U1."area_id")
   WHERE (ST_Contains("U1"."polygon",
 
ST_GeomFromWKB('\\001\\001\\000\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A',
 27700))
   AND U0."generation_high_id" >= 13 AND U0."generation_low_id" <= 13 )
 );
 }}}

 which gives this error:

 {{{
 ERROR:  missing FROM-clause entry in subquery for table "U1"
 LINE 1: ...U1 ON (U0."id" = U1."area_id") WHERE (ST_Contains("U1"."poly...
 }}}

 It appears that the "U1" must '''not''' be quoted in the first argument to
 ST_Contains, as then it works fine:

 {{{
 SELECT "areas_code"."id", "areas_code"."area_id", "areas_code"."type",
 "areas_code"."code"
 FROM "areas_code"
 WHERE "areas_code"."area_id" IN (
   SELECT U0."id" FROM "areas_area" U0
   INNER JOIN "areas_geometry" U1 ON (U0."id" = U1."area_id")
   WHERE (ST_Contains(U1."polygon",
 
ST_GeomFromWKB('\\001\\001\\000\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A',
 27700))
   AND U0."generation_high_id" >= 13 AND U0."generation_low_id" <= 13 )
 );
   id   | area_id |  type   |  code
 ---+-+-+
  14532 |9498 | unit_id | 148
  14531 |9498 | ons | 00CUGA
  44890 |   65890 | unit_id | 24640
  17586 |   11809 | unit_id | 41426
   2324 |2546 | unit_id | 72
   2323 |2546 | ons | 00CU
 (6 rows)
 }}}

 I did look at the code to try and see what does the quoting or inner
 joining, but I'm afraid I couldn't work it out, sorry.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

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