Sorting by distance with a related ManyToMany field

Question

I have this two models.

class Store(models.Model):
    coords = models.PointField(null=True,blank=True)
    objects = models.GeoManager()

class Product(models.Model):
    stores  = models.ManyToManyField(Store, null=True, blank=True)
    objects = models.GeoManager()

I want to get the products sorted by the distance to a point. If the stores field in Product was a Foreign Key I would do this and it works.

pnt = GEOSGeometry('POINT(5 23)')
Product.objects.distance(pnt, field_name='stores__coords').order_by('distance')

But since the field is a ManyToMany field it breaks with

ValueError: <django.contrib.gis.db.models.fields.PointField: coords> is not in list

I kind of expected this because it's not clear which of the stores it should use to calculate the distance, but is there any way to do this.

I need the list of products ordered by distance to a specific point.

Answer 1

Just an idea, maybe this would work for you, this should take only two database queries (due to how prefetch works). Don't judge harshly if it doesn't work, I haven't tried it:

class Store(models.Model):
    coords = models.PointField(null=True,blank=True)
    objects = models.GeoManager()

class Product(models.Model):
    stores  = models.ManyToManyField(Store, null=True, blank=True, through='ProductStore')
    objects = models.GeoManager()

class ProductStore(models.Model):
    product = models.ForeignKey(Product)
    store = models.ForeignKey(Store)
    objects = models.GeoManager()

then:

pnt = GEOSGeometry('POINT(5 23)')
ps = ProductStore.objects.distance(pnt, field_name='store__coords').order_by('distance').prefetch_related('product')
for p in ps:
    p.product ... # do whatever you need with it