That worked. Thanks.
For the record, I'm using MySQL 4.1.21 and MySQLdb 1.2.2-final. Not
sure why I wasn't getting the error.
On Apr 15, 1:53 pm, "Karen Tracey" <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 15, 2008 at 9:48 AM, Aaron <[EMAIL PROTECTED]> wrote:
>
> > I have a model (Sale) that has latitude and longitude fields. I'm
> > trying to take one Sale object and find others within a certain radius
> > (using the great circle formula). When I do this using the extra()
> > QuerySet modifier, it returns a queryset with all the Sale objects in
> > the database. But when I manually execute the raw SQL query that's
> > being run (according to connection.queries), it returns the result
> > that I'm expecting.
>
> > I'm using version 0.97-pre-SVN-7121 with MySQL.
>
> What version of MySQL and which Django mysql backend? Trying to recreate
> with MySQL 5.0.45 the count() on the query set below raises a warning.
> With the current mysql backend that gets turned into an exception thrown, so
> I get no result. When I tried backing up to using the mysql_old backend,
> the warning doesn't (apparently) get turned into an exception (though I
> still see it printed on the console), and the count returns the (incorrect)
> result of all the rows in the table. So I think you must be using the old
> mysql backend? But I don't understand why you do not see the warning I do.
>
> My results are indicated below...
>
>
>
> > Am I doing something wrong?
>
> > From my shell session:
>
> > >>> from data.models import Sale
> > >>> from django.db import connection
>
> > >>> this_sale = Sale.objects.order_by('?')[0]
> > >>> distance_query = "acos( SIN( PI( ) * %s /180 ) * SIN( PI( ) *
> > `data_sale`.`latitude` /180 ) + COS( PI( ) * %s /180 ) * COS( PI( ) *
> > `data_sale`.`latitude` /180 ) * COS( PI( ) * `data_sale`.`longitude` /180 -
> > PI( ) * %s /180 ) ) * 3963.191" % (this_sale.latitude, this_sale.latitude,
> > this_sale.longitude)
>
> > >>> nearby_sales = Sale.objects.extra(where=['%s < %s'],
> > params=[distance_query, .5])
> > >>> nearby_sales.count()
>
> Here I get:
>
> /homedir/django/trunk/django/db/backends/mysql_old/base.py:48: Warning:
> Truncated incorrect DOUBLE value: 'acos( SIN( PI( ) * 99.0 /180 ) * SIN( PI(
> ) * `data_sale`.`latitude` /180 ) + COS( PI( ) * 99.0 /180 ) * COS( PI( ) *
> `data_sale'
> return self.cursor.execute(sql, params)
> 3L
>
> (For the data, I mocked up, there are 3 rows in the table but the correct
> answer should be 1.)
>
> Note the quote before acos( in the warning. I believe the problem is that
> strings you specify in params for the extra are automatically quoted, which
> you do not want to be done to your distance_query parameter. To avoid
> having the distance_query parameter quoted do something like this:
>
> >>> where='%s < %%s' % distance_query
> >>> nearby_sales = Sale.objects.extra(where=[where], params=[.5])
> >>> nearby_sales.count()
>
> 1L
>
>
>
> > 52448L
>
> > >>> query = connection.queries[-1]['sql']
> > >>> query
> > u'SELECT COUNT(*) FROM `data_sale` WHERE acos( SIN( PI( ) * 37.41915 /
> > 180 ) * SIN( PI( ) * `data_sale`.`latitude` /180 ) + COS( PI( ) *
> > 37.41915 /180 ) * COS( PI( ) * `data_sale`.`latitude` /180 ) *
> > COS( PI( ) * `data_sale`.`longitude` /180 - PI( ) * -76.514234 /
> > 180 ) ) * 3963.191 < 0.5'
> > >>> cursor = connection.cursor()
> > >>> cursor.execute(query)
> > 1L
> > >>> cursor.fetchone()[0]
> > 34L
>
> connection.queries doesn't exactly mimic the backend quoting behavior,
> apparently.
>
> Karen
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
