CODEWITHSUNDEEP
pythondjangosorting
Brandon Harmon
Brandon Harmon

Reputation: 69

Django, creating custom order/filter based on value from another Model

I am creating a Reddit clone and currently trying to implement the posts sort functionality. I am stuck on sorting the posts by "most upvotes." I believe I structured my models/database incorrectly and hoping I won't have to redo it because I have my voting system working perfectly.

// models.py
class Post(models.Model):
    title = models.CharField(max_length=300)
    content = models.TextField()
    date_created = models.DateTimeField(verbose_name="date_created", auto_now_add=True)
    date_edited = models.DateTimeField(verbose_name="date_edited", auto_now=True)
    author = models.ForeignKey(User, related_name="posts", on_delete=models.CASCADE)
    subreddit = models.ForeignKey(Subreddit, on_delete=models.CASCADE)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["title", "subreddit"], name="sameTitle_in_sameSubreddit_notAllowed")
        ]

    def __str__(self):
        return self.title

class Vote(models.Model):
    original_post = models.ForeignKey(Post, related_name="post_votes", on_delete=models.CASCADE, null=True)
    original_comment = models.ForeignKey(Comment, related_name="comment_votes", on_delete=models.CASCADE, null=True)
    owner = models.ForeignKey(User, related_name="votes", on_delete=models.CASCADE)
    vote_choice = models.IntegerField(choices=((1, "UP"), (2, "DOWN")))

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["original_post", "owner"], name="sameOwner_samePost_notAllowed"),
            models.UniqueConstraint(fields=["original_comment", "owner"], name="sameOwner_sameComment_notAllowed")
        ]

Looking at the "Votes" model, I was able to write a raw SQL query in a view (testing purposes) that sorts the 'original_post' column by the most upvotes and returns it in a list. Is it possible to take the results of this query and apply a custom sorting to the "Post" queryset. So, for example, sort the Posts by 'post_id' but sort the iDs in the same order of the 'most upvotes list.'

// views.py
class PostWithMostUpVotes(viewsets.ModelViewSet):
        def list(self, request, *args, **kwargs):
            queryset = Vote.objects.raw("SELECT id, original_post_id FROM reddit_api_vote WHERE original_post_id IS NOT NULL AND vote_choice='1' GROUP BY original_post_id ORDER BY count(vote_choice) DESC;")
            serializer = VoteSerializer(queryset, many=True)
            sorted_list = []
            for x in serializer.data:
                sorted_list.append(x["original_post"])
            return Response(sorted_list)

I tested the above view with Postman and it does indeed work as intended.

// urls.py
router.register('mostupvotes', PostWithMostUpVotes, 'mostupvotes')

original_post 9 accounts for the most upVotes. FYI: 1=upvote, 2=downvote

The post with iD of 9 has most upvotes, so it appears at start of the list.

original_post 9 accounts for the most upVotes. FYI: 1=upvote, 2=downvote The post with iD of 9 has most upvotes, so it appears at start of the list.

Upvotes: 0

Views: 91

Answers (2)

Brandon Harmon
Brandon Harmon

Reputation: 69

With the great help of @sytech I was able to perform the query exactly how I wanted. Sort the posts by the greatest number of upvotes in order of most upvotes to least upvotes. I was also able to create a URL path for it within the original viewset.

class PostViewSet(viewsets.ModelViewSet):
    queryset = Post.objects.all()
    serializer_class = PostSerializer

    @action(detail=False)
    def most_upvotes(self, request, *args, **kwargs):
        up_count = Count("post_votes", filter=Q(post_votes__vote_choice=1))
        posts = Post.objects.annotate(up_count=up_count).order_by("-up_count")
        serializer = PostSerializer(posts, many=True)
        return Response(serializer.data)

Upvotes: 0

sytech
sytech

Reputation: 41169

You should take full advantage of all the builtin work of DRF. To do this, all you need to do is implement a get_queryset method.

class PostView(viewsets.ModelViewSet):
    serializer_class = PostSerializer
    queryset = Post.objects.all()

This is close to what you should need for most views in DRF.

Anyhow, if you want to to sort the posts by number of votes, you can do so by simply ordering the queryset. We'll also annotate the query with counts

You can do this in the initial queryset on the class, or you can implement the get_queryset method to have the queryset made dynamically.

class PostView(viewsets.ModelViewSet):
    serializer_class = PostSerializer
    queryset = Post.objects.all()

    def get_queryset(self):
        qs = super().get_queryset()
        with_counts = qs.annotate(vote_count=Count('post_votes'))
        hotness_order = with_counts.order_by('-vote_count')
        return hotness_order

Alternatively, you can also specify ordering fields in DRF to allow for easy ordering based on request that comes from the client. This assumes you have a backend configured for this.

class PostView(viewsets.ModelViewSet):
    serializer_class = PostSerializer
    queryset = Post.objects.annotate(vote_count=Count('post_votes')
    ordering_fields = ['vote_count', 'date_created']

Upvotes: 1

Related Questions