How can I sort by the id of a ManyToManyField in Django?

Question

I've got a ManyToManyField in a user object and it's used to map the users that user is following. I'm trying to show a subset list of who they have most recently followed. Is there a trick in .order_by() that will allow me to sort by the id of the ManyToManyField? The data is there, right?

# (people the user is following)
following = models.ManyToManyField(User, related_name="following", blank=True)

theuser.following.filter(user__is_active=True).order_by("user__id")

That will give me a list of the users the user is following but ordered by when they joined. I want the order of the following list to be in order of when the user followed them.

Interesting/strange question. The id field of the intermediate table used by ManyToMany to do the join is interesting because ... ? — Peter Rowell, Dec 15 '09 at 03:58
@peter i think the id in that table should show the order of the relationships made so he can do his "you recently followed:" idea. — Brandon Henry, Dec 15 '09 at 18:45

score 9 · Answer 1 · answered Feb 16 '17 at 02:16

In fact (at least in Django 1.10), you don't need to use the extra feature but instead can just order by the field directly. Just use the automatically created through table name followed by ".id" as an argument to order_by. E.g.

pizza.toppings.all().order_by('appname_pizza_toppings.id')

article.tags.all().order_by('appname_article_tags.id')

For this particular question:

theuser.following.filter(user__is_active=True)\ .order_by("appname_user_user_following.id")

Many other solutions suggest creating a custom through table and adding a field but if you just want to sort by the id of the automatically generated through table then this is not necessary.

Ry4an Brase · Answer 2 · 2015-11-23T17:03:20.383

I just found a way to do this without having to create a class for the relationship. It relies on the extra feature that lets you add additional columns to output. In your example it would look like:

theuser.following.filter(user__is_active=True)\
    .extra(select={'creation_seq': 'appname_user_user_following.id'})\
    .order_by("creation_seq")

Notice that appname_user_user_following is the name of the relationship table Django creates under the covers. It's deterministic and something you can get and set via meta-mechanisms, but it's pretty much safe to hardcode.

Here's an example of the SQL that's being created under the covers with fake table and columns names:

SELECT (appname_user_user_following.id) AS `creation_seq`, `appname_user`.`id`
FROM `appname_user` INNER JOIN `appname_user_user_following` ON
(`appname_user`.`id` = `appname_user_user_following`.`user_id`) WHERE
`appname_user_user_following`.`user_followed_id` = 1  ORDER BY `creation_seq` ASC';

That worked for me. You should put the code after 'select' into braces, as it requires a dict. — Archarachne, Nov 20 '15 at 08:06

Dušan Maďar · Answer 3 · 2018-03-14T10:38:57.487

5

Tested with Django 1.11.10.

You don't have to hardcode the relationship table name (How to read the database table name of a Model instance?).

So an update on @Ry4an Brase's answer can look like

recently_followed = '-{}.id'.format(theuser.following.through._meta.db_table)  
theuser.following.filter(user__is_active=True).order_by(recently_followed)

edited Mar 14 '18 at 10:38

answered Mar 14 '18 at 10:32

Dušan Maďar

9,269
5
49
64

This should be the accepted answer. Thanks @Dušan Maďar – David Guillot Dec 16 '20 at 09:50

score 0 · Answer 4 · answered Dec 15 '09 at 23:28

I am not sure whether you can achieve this with a regular ManytoManyField. You could try defining the intermediate model explicitly.

nb: Untested code!

class Person(models.Model)
    name = models.CharField(max_length=30)

class FollowerRelationship(models.Model)
    follower = models.ForeignKey(Person, related_name = following_set)
    following = models.ForeignKey(Person, related_name = follower_set)

You can then create following relationships in the shell as follows.

# Create Person objects
>>> a = Person(name="Alice")
>>> a.save()
>>> b = Person(name="Bob")
>>> b.save()
>>> c = Person(name="Chris")
>>> c.save()

# Let Alice follow Chris and Bob 
>>> FollowerRelationship.objects.create(follower=a, following=c)
>>> FollowerRelationship.objects.create(follower=a, following=b)

You can create a queryset of FollowerRelationship objects where Alice is the follower, ordered by the id of the join table, with the line:

>>> qs = FollowerRelationship.objects.filter(follower=a).order_by('id')
>>> [fr.following for fr in qs]

Note that you have to loop through the FollowerRelationship objects, to get the 'followed' Person in the relationship.

You may also want to look at Extra fields on many-to-many relationships in the Django docs, which describes how to specify the intermediate model in a many-to-many relationship.

How can I sort by the id of a ManyToManyField in Django?

4 Answers4

Linked

Related