CODEWITHSUNDEEP
djangogeodjangodjango-orm
manuel
manuel

Reputation: 847

Sorting by distance with a related ManyToMany field

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.

Upvotes: 8

Views: 493

Answers (3)

philipxy
philipxy

Reputation: 15158

I will take "distance from a product to a point" to be the minimum distance from the point to a store with that product. I will take the output to be a list of (product, distance) for all products sorted by distance ascending. (A comment by someone who placed a bounty indicated they sometimes also want (product,distance,store) sorted by distance then store within product.)

Every model has a corresponding table. The fields of the model are the columns of the table. Every model/table should have a fill-in-the-(named-)blanks statement where its records/rows are the ones that make a true statement.

Store(coords,...) // store [store] is at [coords] and ...
Product(product,store,...) // product [product] is stocked by store [store] and ...

Since Product has store(s) as manyToManyField it already is a "ProductStore" table of products and stocking stores and Store already is a "StoreCoord" table of stores and their coordinates.

You can mention any object's fields in a query filter() for a model with a manyToManyField.

The SQL for this is simple:

select p.product,distance
    select p.product,distance(s.coord,[pnt]) as distance
    from Store s join Product p
    on s.store=p.store
group by product
having distance=min(distance)
order by distance

It should be straightforward to map this to a query. However, I am not familiar enough with Django to give you exact code now.

from django.db.models import F

q = Product.objects.all()
    .filter(store__product=F('product'))
    ...
    .annotate(distance=Min('coord.distance([pnt])'))
    ...
    .order_by('distance')

The Min() is an example of aggregation.

You may also be helped by explicitly making a subquery.

It is also possible to query this by the raw interface. However, the names above are not right for a Django raw query. Eg the table names will by default be APPL_store and APPL_product where APPL is your application name. Also, distance is not your pointField operator. You must give the right distance function. But you should not need to query at the raw level.

Upvotes: 0

lehins
lehins

Reputation: 9767

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

Upvotes: 1

manuel
manuel

Reputation: 847

This is how I solved it but I dont really like this solution. I think is very inefficient. There should be a better way with GeoDjango. So, until i find a better solution I probably wont be using this. Here's what I did.

I added a new method to the product model

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

    def get_closes_store_distance(point):
        sorted_stores =  self.stores.distance(point).order_by('distance')
        if sorted_stores.count() > 0:
            store = sorted_stores[0]
            return store.distance.m
        return 99999999 # If no store, return very high distance

Then I can sort this way

def sort_products(self, obj_list, lat, lng):
    pt = 'POINT(%s %s)' % (lng, lat)
    srtd = sorted(obj_list, key=lambda obj: obj.get_closest_store_distance(pt))
    return srtd

Any better solutions or ways to improve this one are very welcome.

Upvotes: 0

Related Questions