Order queryset by field in intermediary table with recursive m2m relationship-CodePudding

I have followers/followees system in my django app and I want to order by user's followers and followees by action time when appropriate action is happend. My models:

class UserFollow(models.Model):
    followee = models.ForeignKey('CustomUser', on_delete=models.CASCADE, related_name='follower_set') # The one who is followed
    follower = models.ForeignKey('CustomUser', on_delete=models.CASCADE, related_name='followee_set') # The one who followed
    timestamp = models.DateTimeField(auto_now_add=True)

class CustomUser(AbstractBaseUser, PermissionsMixin):
    # Other fields which don't matter in this question
    followers = models.ManyToManyField('self', blank=True, related_name='followees', symmetrical=False, through='UserFollow')

For example i have user and i can get all his followers in reverse order when they followed this user:

followers = user.followers.all()
# returns <QuerySet [<CustomUser: follower1>, <CustomUser: follower2>, ... , <CustomUser: follower{n}>]

The following qs of course doesn't work:

followers = user.followers.order_by('-userfollow__timestamp')

I can get followers pk in right order and then get followers objects:

followers_pk = user.follower_set.values_list('follower', flat=True).order_by('-timestamp')
# returns <QuerySet [n, ... , 3, 2, 1]>
followers = CustomUser.objects.filter(pk__in=followers_pk)
# returns <QuerySet [<CustomUser: follower1>, <CustomUser: follower2>, ... , <CustomUser: follower{n}>]> (user objects in reverse order)

Any suggestions or tips to achieve what i want?

CodePudding user response：

Here is a simplified example:

class Doctor(...):
    patients = models.ManyToMany('Patient', ..., related_name = 'doctors', through = 'Registration')

class Patient(...):
    doctors = models.ManyToMany('Doctor', ..., related_name = 'patients', through = 'Registration')

class Registration(...):
    patient = models.ForeignKey('Patient', ..., related_name = 'registrations')
    doctor = models.ForeignKey('Doctor', ..., related_name = 'registrations')
    timestamp = models.DateTime(...)

Then we can do:

# get a specific doctor:
doctor = Doctor.objects.get(id='<doctor-id>')

# get and order all patients that registered with this doctor by the registration timestamp:
patients = Patient.objects.filter(doctors=doctor).order_by('registrations__timestamp')

In your example this would look like:

# the user we want the followers of:
celebrity = CustomUser.objects.get(id='<celebrity-id>')

# their followers ordered by timestamp:
followers = CustomUser.objects.filter(followees=celebrity).order_by('follower_set__timestamp')