[FIXED] Groupby and Count doesn't include users that have no posts (OneToMany relationship between two models)

Issue

I have two models, User and Post, where each user can have many posts.

class User(models.Model):
    first_name = models.CharField("First name", max_length=150)
    last_name = models.CharField("Last name", max_length=150)


class Post(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="posts")
    content = models.CharField(max_length=300)

Now for a sample data like this:

User
id    first_name    last_name
1     Jon           Skeet
2     Gordon        Linoff
3     Another       Soul

Post
user  content
1     Jon #1
1     Jon #2
1     Jon #3
2     Gordon #1
2     Gordon #2

I'm trying to create an API that returns a response like this:

[
    {
        "first_name": "Jon",
        "last_name": "Skeet",
        "posts_count": "3",
        "recent_posts": [
            {
                "content": "Jon #1",
                "created": "2022-07-22T07:48:12.299032Z"
            },
            {
                "content": "Jon #2",
                "created": "2022-07-22T07:47:26.830772Z"
            },
            {
                "content": "Jon #3",
                "created": "2022-07-22T07:02:31.654366Z"
            }
        ]
    },
    {
        "first_name": "Gordon",
        "last_name": "Linoff",
        "posts_count": "2",
        "recent_posts": [
            {
                "content": "Gordon #1",
                "created": "2022-07-22T09:59:36.965825Z"
            },
            {
                "content": "Gordon #2",
                "created": "2022-07-22T09:59:18.544077Z"
            },
        ]
    },
    {
        "first_name": "Another",
        "last_name": "Soul",
        "posts_count": "0",
        "recent_posts": []
    }
]

So for each user, I want to include these info in the result:

1. The count of their posts
2. Their top three most recent posts

Here's what I've done so far. I created two serializers, one for each model:

class UserSerializer(serializers.ModelSerializer):
    posts_count = serializers.IntegerField(required=False)
    recent_posts = serializers.ListField(required=False)

    class Meta:
        model = User
        fields = ("first_name", "last_name", "posts_count", "recent_posts")


class PostsSerializer(serializers.ModelSerializer):
    class Meta:
        model = Post
        fields = ("content", "created")

And then I count the posts, and for each user I fetch the top three most recent posts and return the serialized data:

@api_view(["GET"])
def get_user(request):
    users = (
        User.objects
        .filter(posts__isnull=False)
        .values("id", "first_name", "last_name")
        .annotate(posts_count=Count("id"))
    )

    for user in users:
        recent_addresses = (
            Post.objects
            .filter(user=user["id"])
            .order_by("-created")
        )[:3]

        user["recent_posts"] = PostsSerializer(recent_addresses, many=True).data

    serializer = UserSerializer(users, many=True)
    return Response(serializer.data)

But the result is this:

[
    {
        "first_name": "Jon",
        "last_name": "Skeet",
        "posts_count": 3,
        "recent_posts": [
            {
                "content": "Jon #3",
                "created": "2022-07-22T14:02:00.928810Z"
            },
            {
                "content": "Jon #2",
                "created": "2022-07-22T14:01:51.328254Z"
            },
            {
                "content": "Jon #1",
                "created": "2022-07-22T14:01:41.935594Z"
            }
        ]
    },
    {
        "first_name": "Gordon",
        "last_name": "Linoff",
        "posts_count": 2,
        "recent_posts": [
            {
                "content": "Gordon #2",
                "created": "2022-07-22T14:02:16.865880Z"
            },
            {
                "content": "Gordon #1",
                "created": "2022-07-22T14:02:08.371927Z"
            }
        ]
    }
]

As you can see, it didn't include users that have no posts. I can understand why, because I've explicitly said .filter(posts__isnull=False). But I don't know how to solve this. Now I can't remove that filter, or it will return 1 for every posts_count. I believe I've complicated things more than necessary with the for loop, but don't know any other way. How can I solve this?

Solution

Try this

users = (
        User.objects
        .values("id", "first_name", "last_name")
        .annotate(posts_count=Count("posts"))
    )

Answered By - Waket Zheng