Django按计算字段排序 [英] Django Sort By Calculated Field
问题描述
使用距离逻辑此SO post ，我正在使用以下代码恢复正确筛选的一组对象：
Using the distance logic from this SO post, I'm getting back a properly-filtered set of objects with this code:
class LocationManager(models.Manager):
def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
if use_miles:
distance_unit = 3959
else:
distance_unit = 6371
from django.db import connection, transaction
cursor = connection.cursor()
sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
AS distance FROM locations_location HAVING distance < %d
ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
cursor.execute(sql)
ids = [row[0] for row in cursor.fetchall()]
return self.filter(id__in=ids)
问题是我无法弄清楚如何保持列表/查询器按距离值排序。由于性能原因（一个查询与我的数据库中每个潜在位置的一个查询相比），我不想这样做为extra（）方法调用。几个问题：
The problem is I can't figure out how to keep the list/ queryset sorted by the distance value. I don't want to do this as an extra() method call for performance reasons (one query versus one query on each potential location in my database). A couple of questions:
- 如何按距离排序我的列表？即使脱离我在我的模型中定义并使用order_by（）的本机类型，它仍然以其他方式排序（id，我相信）。
- 我错了性能和Django将优化查询，所以我应该使用extra（）？
- 这是完全错误的方法，我应该使用地理库而不是手动滚动
推荐答案
以相反的顺序回答您的问题：
To take your questions in reverse order:
Re 3）是的，如果您正在使用地理空间数据，您一定要利用PostGIS和GeoDjango。这只是愚蠢的。
Re 3) Yes, you should definitely take advantage of PostGIS and GeoDjango if you're working with geospatial data. It's just silly not to.
Re 2）我不认为你可以让Django对你使用.extra（）（禁止接受<一个href =http://code.djangoproject.com/ticket/10702 =nofollow noreferrer>这张票），但它是Django 1.2中新的.raw（）方法的绝佳候选者（见下文）。
Re 2) I don't think you could quite get Django to do this query for you using .extra() (barring acceptance of this ticket), but it is an excellent candidate for the new .raw() method in Django 1.2 (see below).
Re 1）您将从第一个查询获取ids列表，然后使用in查询获取对象的QuerySet对应于这些ID。您的第二个查询无法访问与第一个查询的计算距离;它只是获取一个ids列表（它也不在乎你提供这些ids的什么顺序）。
Re 1) You are getting a list of ids from your first query, and then using an "in" query to get a QuerySet of the objects corresponding to those ids. Your second query has no access to the calculated distance from the first query; it's just fetching a list of ids (and it doesn't care what order you provide those ids in, either).
可能的解决方案（缺少所有这些和使用GeoDjango）：
Possible solutions (short of ditching all of this and using GeoDjango):
-
升级到Django 1.2测试版，并使用 new .raw（）方法。这允许Django智能地解释原始SQL查询的结果，并将其转换为实际模型对象的QuerySet。这将将您当前的两个查询减少到一个，并保留在SQL中指定的排序。这是最好的选择，如果你能够升级。
Upgrade to Django 1.2 beta and use the new .raw() method. This allows Django to intelligently interpret the results of a raw SQL query and turn it into a QuerySet of actual model objects. Which would reduce your current two queries into one, and preserve the ordering you specify in SQL. This is the best option if you are able to make the upgrade.
不要打扰Django查询器或Django模型对象，只需添加所有您需要的字段在原始SQL SELECT中，然后直接从游标中使用这些行。如果您需要模型方法等，可能不是一个选择。
Don't bother constructing a Django queryset or Django model objects at all, just add all the fields you need into the raw SQL SELECT and then use those rows direct from the cursor. May not be an option if you need model methods etc later on.
在Python代码中执行第三步，您可以在其中迭代查询集并构建Python与第一个查询返回的ids列表的顺序相同的模型对象列表。返回该列表而不是QuerySet。如果您需要进一步过滤线路，将无法正常工作。
Perform a third step in Python code, where you iterate over the queryset and construct a Python list of model objects in the same order as the ids list you got back from the first query. Return that list instead of a QuerySet. Won't work if you need to do further filtering down the line.
这篇关于Django按计算字段排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
