QuerySet order_by intermediate models

Question

I'm trying to create a intermediate model with the created field recording the time of following relationship.

The models is as following:

class Profile(models.Model):
    user = models.OneToOneField(User, related_name='profile')
    realname = models.CharField(max_length=20, verbose_name='真實姓名', blank=True)
    nickname = models.CharField(max_length=20, verbose_name='暱稱/顯示名稱')

## the intermediate model is connected through following field  ##
    followings = models.ManyToManyField('self', through='FollowShip',
                                        related_name='followers', symmetrical=False,
                                        through_fields=('profile_from', 'profile_to'))

    total_followers = models.IntegerField(default=0)

    def __str__(self):
        return 'Profile of User:{}'.format(self.user.username)


class FollowShip(models.Model):
    profile_from = models.ForeignKey(Profile, related_name='follow_from_set')
    profile_to = models.ForeignKey(Profile, related_name='follow_to_set')
    created = models.DateField(auto_now_add=True, db_index=True)

    def __str__(self):
        return "{} follows {}".format(self.profile_from, self.profile_to)

    class Meta:
        unique_together = ('profile_from', 'profile_to')

It works fine. However, when I tried to access the QuerySet order by created field of FollowShip model. The error occurs.

In manage.py shell:

>>> user.profile.followings.order_by('followship__created')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/query.py", line 232, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/query.py", line 256, in __iter__
    self._fetch_all()
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/query.py", line 1087, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/query.py", line 54, in __iter__
    results = compiler.execute_sql()
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 824, in execute_sql
    sql, params = self.as_sql()
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 369, in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup()
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 47, in pre_sql_setup
    order_by = self.get_order_by()
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 293, in get_order_by
    field, self.query.get_meta(), default_order=asc))
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 553, in find_ordering_name
    field, targets, alias, joins, path, opts = self._setup_joins(pieces, opts, alias)
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 586, in _setup_joins
    pieces, opts, alias)
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1402, in setup_joins
    names, opts, allow_many, fail_on_missing=True)
  File "/Users/young/Desktop/env/strayvoice/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1327, in names_to_path
    "Choices are: %s" % (name, ", ".join(available)))
django.core.exceptions.FieldError: Cannot resolve keyword 'followship' into field. Choices are: follow_from_set, follow_to_set, followers, followings, id, nickname, realname, total_followers, user, user_id

It looks like the intermediate model was not set correctly. So that I cannot find followship while query. However, I can access both sides well. For examples:

>>> user.profile.followings.all()
`<QuerySet [<Profile: Profile of User:222222>, <Profile: Profile of User:333333>]>`
>>> user.profile.followers.all()
`<QuerySet [<Profile: Profile of User:222222>]>`

Have no clues what's go wrong.

Answer 1

Use p.follow_from_set.order_by('created').

user.profile.followings means the profile who the user.profile has followed.
user.profile.followers means the profile who have followed the user.profile.
So both the user.profile.followers and user.profile.followings will return a queryset of Profile, they can only be ordered by the fields of Profile, such as user, realname, nickname.

If you want to find the profile which has been followed by use.profile and order_by created, you can use:

result = []
for profile_id in user.profile.follow_from_set.order_by('created').values('profile_to'):
    result.append(Profile.objects.get(id=profile_id))

If there are many following, maybe you can optimize the sql by this:

profile_ids = user.profile.follow_from_set.order_by('created').values('profile_to')
profiles = Profile.objects.filter(id__in=profile_ids)

FInally, reorder the profiles with the order of profile_ids.

result = []
result_dict = {}
for profile in profiles:
    result_dict[profile.id] = profile
for id in profile_ids:
    result.append(result_dict[id])
return result

Maybe there is many better way to solve the problem. Can any one make a better resolution?