CODEWITHSUNDEEP
django
deadlock
deadlock

Reputation: 7310

Django: Order a queryset by it's ManyToMany primary key in descending order

Let's say I have a Photo Model and a Hashtag model

class Post(models.Model):
    user = models.ForeignKey(User, related_name = 'posts_owned')
    created = models.DateTimeField(auto_now_add=True)
    description = models.CharField(max_length=255, blank=True)
    media_url = models.URLField(max_length=225, blank=True)
    related_hashtag = models.ManyToManyField('Hashtag', related_name = 'tagged_post', null=True, blank=True)

class Hashtag(models.Model):
    hashtag = models.CharField(max_length=60, unique = True)
    count = models.IntegerField(default=1)

How can I query posts in descending order of insertion for the ManyToMany field? The ManyToMany table has primary keys for each row and I would like to sort by the ID of the ManyToMany field.

I tried the following:

queryset = Post.objects.filter(related_hashtag__hashtag = hashtag).order_by('-related_hashtag__id')

But that doesn't work. I need to sort it by the PK in the related_hashtag table.

Is this possible? Should I use raw SQL for this? If I we're to use raw SQL, what would it look like?

For now, I'm doing it like this:

queryset = Post.objects.filter(related_hashtag__hashtag = hashtag).order_by(-pk)

But the problem here is that this is ordering it by the Post ID's in descending order. I also have a Comment model from which hashtags can be created and attached to the Post. So ordering by Post PK won't work. Any Suggestions or solutons?

Upvotes: 4

Views: 2082

Answers (4)

Benoit Blanchon
Benoit Blanchon

Reputation: 14511

The jazz-band library django-sortedm2m provides a drop-in replacement for ManyToManyField that adds a hidden ordering field (named "sort_value_field_name", by default).

As they put it:

The provided SortedManyToManyField behaves like the original one but remembers the order of added relations.

I'm don't why they don't use the private key, though.

Upvotes: 0

pcoronel
pcoronel

Reputation: 3971

You can use an extra() modifier to pull the pk from the ManyToMany table:

Post.objects.extra(select={
    'm2m_pk': '''
        SELECT id
        FROM appname_post_related_hashtag
        WHERE appname_post_related_hashtag.hashtag_id={}
            AND appname_post_related_hashtag.post_id=appname_post.id
     '''.format(hashtag)
}).order_by('-m2m_pk')

Upvotes: 0

steffens21
steffens21

Reputation: 759

I hope I understood this question right. Well I give it a try. You can explicitly define the link between your Post and Hashtag classes like this:

class Post(models.Model):
    user = models.ForeignKey(User, related_name = 'posts_owned')
    created = models.DateTimeField(auto_now_add=True)
    description = models.CharField(max_length=255, blank=True)
    media_url = models.URLField(max_length=225, blank=True)
    related_hashtag = models.ManyToManyField('Hashtag', related_name = 'tagged_post', null=True, blank=True, through='PostHashtagLinker')

class Hashtag(models.Model):
    hashtag = models.CharField(max_length=60, unique = True)
    count = models.IntegerField(default=1)

class PostHashtagLinker(models.Model):
    hashtag = models.ForeignKey(Hashtag)
    post = models.ForeignKey(Post)

Then you can form a query like this:

orderedPosts = PostHashtagLinker.objects.filter(hashtag=my_hashtag).order_by(-pk).values(post)

(This ordered list of posts may contain duplicates.)

Upvotes: 2

RodrigoOlmo
RodrigoOlmo

Reputation: 714

You can achieve the same that @steffens21 without defining the intermediate model:

Post.related_hashtag.through.objects.filter(id=my_hashtag).order_by('-pk').values('post')

The problem is that it doesn't return a QuerySet, but a ValuesQuerySet. And it will contain duplicates too...

Upvotes: 1

Related Questions