I hope this is the right place for this. I’ll try to describe it as simply as possible, but my head is spinning a bit after a few hours of googling. I have a Building model, and I want to add a few annotations for a particular client, so for each Building I’m calculating distance from the client, and how many of the things the clients wants (does it have a pool, AC included, etc.) are satisfied. I can do that easily enough with something like:
buildings = Building.objects.all() buildings = buildings.annotate(distance=Distance('location', client_location)) # This is an example because I can't share the real code, but assume this gives the number of requests matched buildings = buildings.annotate(matches=____) buildings.order_by('matches', 'distance')
The part that ruins it for me is that the client also doesn’t want me to recommend more than one building in the same neighborhood. So what I’m trying to do is first sort by matches and distance, and then eliminate any duplicate neighborhoods. So something like
buildings.order_by('matches', 'distance').distinct('neighborhood'). That gives me this error:
django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
I can solve it by starting the order_by with neighborhood, but then they’re no longer ordered correctly. There’s a Django bug report that brings up a similar problem which suggests solving it with a subquery, but if I do that I have to recompute the annotations which are somewhat slow, because they are in the subquery. So is there any way to make this work?