CODEWITHSUNDEEP
pythondjango
Llanilek
Llanilek

Reputation: 3466

Ordering queryset based on related M2M fields

I'm currently trying to return a queryset based off of three models.

Location model

class Location(models.Model):
    ...
    lat = models.FloatField()
    lng = models.FloatField()

User model (extended from Django User)

class UserProfile(models.Model):
    ...
    user = OneToOneField(User)
    locations = ManyToManyField(Location)

Case Model

class Case(models.Model):
    ...
    owner = models.ForeignKey(User)
    completed = models.BooleanField()

I'm using geopy to return locations close to an inputted lat/long 

def get_locations_nearby_coords(latitude, longitude, max_distance=None):
    """
    Return objects sorted by distance to specified coordinates
    which distance is less than max_distance given in kilometers
    """
    # Great circle distance formula
    gcd_formula = "6371 * acos(cos(radians(%s)) * cos(radians(lat)) * cos(radians(long) - radians(%s)) + sin(radians(%s)) * sin(radians(lat)))"
    distance_raw_sql = RawSQL(
        gcd_formula,
        (latitude, longitude, latitude)
    )
    qs = Location.objects.all().annotate(
        distance=distance_raw_sql).order_by('distance')
    if max_distance is not None:
        qs = qs.filter(distance__lt=max_distance)
    return qs

What I'm aiming to return, is the 10 closest locations ordered by the number of completed cases for each UserProfile

A user can have many locations, and a location can be associated with many users hence why I went with an m2m field.

I can't have any duplicates, however, a location can show twice in the list IF there are multiple users at that location, but the final list should be sliced at 10.

EDIT:

Trying to clarify what should be returned.

Let's say I have 3 locations that are close to me (this is already known data) 

1. Location 1 (Closest)
2. Location 2 
3. Location 3 (Furthest)

Location 1 Has 1 user, who's completed 3 cases. Location 2 has two users, user 1 has completed 10 cases, and user 2 has completed 2. Location 3 has 1 user with 4 completed cases. What should be returned is as follows

1. Location 2 - User 1 - 10 Cases
2. Location 3 - User 1 - 4 Cases
3. Location 1 - User 1 - 3 Cases
4. Location 2 - User 2 - 2 Cases

Upvotes: 1

Views: 54

Answers (1)

IVNSTN
IVNSTN

Reputation: 9299

First of all I'd suggest to apply explicit relation names to use them in queries.

To append completed case count to your queryset you can use this:

qs = qs.annotate(completed_cases=Count('user__case', filter=Q(user__case__completed=True))

and the final slicing is as trivial as [:10]

qs = qs.order_by('distance', '-completed_cases`, 'id', )[:10]

id - to avoid randomness for the same distance and case count.

UPD

# finding locations
...
qs = qs.order_by('distance', 'id', )[:10]

# finding users with completed cases
final_qs = User.objects.filter(locations__in=qs, ).\
    annotate(
        completed_cases=Count('case', filter=Q(case__completed=True),
        location_id=F('locations__id'),
    ).order_by('-completed_cases', 'id')[:10]

Upvotes: 1

Related Questions