[Answer]-Custom SQL for Geodjango on ForignKey

by

1👍

✅

Since the geometry you’re measuring distance to is on UserProfile, it makes sense to query for UserProfile objects and then handle each Item object they own. (The distance is the same for all items owned by a profile.)

For example:

all_profiles = UserProfile.objects.all()
for profile in all_profiles.distance(p).order_by('distance'):
   for item in profile.item_set.all():
       process(item, profile.distance)

You may be able to make this more efficient with prefetch_related:

all_profiles = UserProfile.objects.all()
all_profiles = all_profiles.prefetch_related('item_set')  # we'll need these
for profile in all_profiles.distance(p).order_by('distance'):
   for item in profile.item_set.all():  # items already prefetched
       process(item, profile.distance)

If it’s important for some reason to query directly for Item objects, try using extra:

items = Item.objects.all()
items = items.select_related('owner')
distance_select = "st_distance_sphere(core_userprofile.location, ST_GeomFromEWKT('%s'))" % p.wkt
items = items.extra({'distance': distance_select})
items = items.order_by('distance')

Raw queries are another option, which let you get model objects from a raw SQL query:

items = Item.objects.raw("SELECT core_item.* FROM core_item JOIN core_userprofile ...")
👤tcarobruce

Leave a comment