Django 按计算字段排序
使用这篇文章中的距离逻辑，我用以下代码返回一组经过正确过滤的对象：
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() 方法调用来执行。有几个问题：
- 如何按距离对列表进行排序？即使取消我在模型中定义的本机排序并使用“order_by()”，它仍然按其他内容（我相信 id）进行排序。
- 我对性能的看法是否错误，Django 会优化查询，所以我应该使用 extra() 代替？
- 这是完全错误的方法吗？我应该使用地理库，而不是像普茨一样手动滚动它？
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)
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:
- How can I sort my list by distance? Even taking off the native sort I've defined in my model and using "order_by()", it's still sorting by something else (id, I believe).
- Am I wrong about the performance thing and Django will optimize the query, so I should use extra() instead?
- Is this the totally wrong way to do this and I should use the geo library instead of hand-rolling this like a putz?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
以相反的顺序回答您的问题：
Re 3) 是的，如果您正在处理地理空间数据，您绝对应该利用 PostGIS 和 GeoDjango。不这样做真是愚蠢。
Re 2）我认为你不能完全让 Django 使用 .extra() 为你做这个查询（除非接受 这张票），但它是 Django 1.2 中新的 .raw() 方法的绝佳候选者（见下文）。
回复 1) 您将从第一个查询中获取 id 列表，然后使用“in”查询来获取与这些 id 对应的对象的 QuerySet。您的第二个查询无法访问第一个查询计算出的距离；它只是获取 id 列表（并且它也不关心您提供这些 id 的顺序）。
可能的解决方案（除了放弃所有这些并使用 GeoDjango）：
升级到 Django 1.2 beta 并使用 新的 .raw() 方法。这使得 Django 能够智能地解释原始 SQL 查询的结果并将其转换为实际模型对象的查询集。这会将当前的两个查询减少为一个，并保留您在 SQL 中指定的顺序。如果您能够升级，这是最好的选择。
根本不用费心构建 Django 查询集或 Django 模型对象，只需将所需的所有字段添加到原始 SQL SELECT 中，然后直接从游标使用这些行即可。如果您稍后需要模型方法等，则可能不是一个选择。
在 Python 代码中执行第三步，迭代查询集并按照与从第一个查询返回的 ids 列表相同的顺序构造模型对象的 Python 列表。返回该列表而不是查询集。如果您需要进一步过滤，则不起作用。
To take your questions in reverse order:
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) 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) 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).
Possible solutions (short of ditching all of this and using GeoDjango):
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.
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.
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.