DEV Community

How can I make a list of group data by month-year in rest Framework with json output?

Borhan Tipu on August 01, 2019

The json data format I want like this: [ { monthName: "AUG 2019", items: [ { day: "03", description: "Baseball", ...
Read full post
Collapse
 
lord007tn profile image
Raed Bahri • • Edited

i have a solution that I've tried and worked for me.

models.py 


class Item(models.Model):

    time = models.IntegerField()
    description = models.CharField(max_length=50)

class GroupData(models.Model):

    item = models.ForeignKey(Item, related_name='items', on_delete=models.CASCADE)

    date = models.DateField()

serilizer.py 


import datetime
class GroupDataSerializer(serializers.ModelSerializer):

    date = serializers.SerializerMethodField(method_name='get_date')
    class Meta:
        model = GroupData
        fields =('__all__')

    def get_date(self, instance):
        date = datetime.datetime.now()
        return date.strftime("%m/%d/%Y")

views.py 


class GroupDataView(generics.ListAPIView):

    queryset = GroupData.objects.all()
    serializer_class = GroupDataSerializer
    
    def get_queryset(self):
        queryset = self.queryset

        return queryset.objects.filter(data=id).order_by('-date')

hope it works for you. DM me if you have a problem

Collapse
 
tipu profile image
Borhan Tipu •

My Model Is

class Event(models.Model):
title = models.CharField(max_length=255, default=None, unique=True)
description = models.TextField()
date = models.DateField(default=None, null=True)

I want to group the from the date field with month-year( like 01-2017 or JAN-2017)

I see that you created another model for that. Can I do it with single model?

Collapse
 
lord007tn profile image
Raed Bahri •

yeah you can but as a best practice if you have a data that require more than one field just put it in a class and refer to it as a foreign key

so it will be a class for your event that have date and name
and a class for group of event or category that take events as foreign keys
in django admin you can control that easily

Thread Thread
 
tipu profile image
Borhan Tipu •

Ok. Let me try this. If it is worked that would be great

Collapse
 
tipu profile image
Borhan Tipu •

Here are my codes

models.py

class Event(models.Model):
    title = models.CharField(max_length=255, default=None, unique=True)
    link = models.URLField(default=None, null=True)
    description = models.TextField()

    def __str__(self):
        return self.title

    class Meta:
        db_table = 'events'

class EventGroup(models.Model):
    event = models.ForeignKey(Event, related_name='events', on_delete=models.CASCADE)
    event_date = models.DateField()

    class Meta:
        db_table = 'event_group'

views.py

class EventGroupData(ListAPIView):
    queryset = EventGroup.objects.all()
    serializer_class = EventGroupSerializer

    def get_queryset(self):
        queryset = self.queryset

        return queryset.objects.filter(data=id).order_by('-event_date')

Serializer.py

import datetime
class EventGroupSerializer(serializers.ModelSerializer):
    event_date = serializers.SerializerMethodField(method_name='get_date')
    class Meta:
        model = EventGroup
        fields = '__all__'

    def get_date(self, instance):
        event_date = datetime.datetime.now()
        return event_date.strftime("%m/%d/%Y")
Collapse
 
tipu profile image
Borhan Tipu •

can you please tell me why same month showing twice with same data?
models.py

class Event(models.Model):
    title = models.CharField(max_length=255, default=None, unique=True)
    link = models.URLField(default=None, null=True)
    description = models.TextField()
    date = models.DateField(default=None, null=True)
    is_published = models.BooleanField(default=False)
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    class Meta:
        db_table = 'events'
        verbose_name = 'event'
        verbose_name_plural = 'events'

class EventGroup(models.Model):
    event = models.ForeignKey(Event, related_name='events', on_delete=models.CASCADE)
    date = models.DateField()
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    class Meta:
        db_table = 'event_group'

serializer.py

class EventGroupSerializer(serializers.ModelSerializer):
    events = serializers.SerializerMethodField(method_name='get_events')
    month_year = serializers.SerializerMethodField(method_name='get_month_year')

    total = serializers.SerializerMethodField(method_name='get_count_events')
    class Meta:
        model = EventGroup
        fields = ('month_year', 'total', 'events',)

    def get_count_events(self, instance):
        org_date = str(instance['date'])
        date = datetime.datetime.strptime(org_date, "%Y-%m-%d")
        month = date.month
        year = date.year
        count = Event.objects.filter(date__month=month, date__year=year).count()
        return count

    def get_month_year(self, instance):
        org_date = str(instance['date'])
        date = datetime.datetime.strptime(org_date, "%Y-%m-%d").date()
        month_year = str(date.month)+'-'+str(date.year)
        return str(month_year)

    def get_events(self, instance):
        org_date = str(instance['date'])
        date = datetime.datetime.strptime(org_date, "%Y-%m-%d")
        month = date.month
        year = date.year
        events = Event.objects.filter(date__month=month, date__year=year)
        event_serializer = EventSerializer(events, many=True)
        return event_serializer.data

views.py

class EventGroupData(ListAPIView):
    queryset = EventGroup.objects.all()

    serializer_class = EventGroupSerializer


    def get_queryset(self):
        queryset = self.queryset

        return queryset.annotate(month=TruncMonth('date')).values('date').annotate(c=Count('id')).order_by()

results

{
    "count": 4,
    "next": null,
    "previous": null,
    "results": [
        {
            "month_year": "6-2019",
            "events": [
                {
                    "id": 30,
                    "title": "Event 2",
                    "description": "Event 2",
                    "date": "2019-06-02"
                },
                {
                    "id": 31,
                    "title": "Event 3",
                    "description": "Event 3",
                    "date": "2019-06-04"
                }
            ]
        },
        {
            "month_year": "6-2019",
            "events": [
                {
                    "id": 30,
                    "title": "Event 2",
                    "description": "Event 2",
                    "date": "2019-06-02"
                },
                {
                    "id": 31,
                    "title": "Event 3",
                    "description": "Event 3",
                    "date": "2019-06-04"
                }
            ]
        },
        {
            "month_year": "9-2019",
            "events": [
                {
                    "id": 32,
                    "title": "Event 4",
                    "description": "Event 4",
                    "date": "2019-09-04"
                },
                {
                    "id": 29,
                    "title": "Event 1",
                    "description": "Event 1",
                    "date": "2019-09-25"
                }
            ]
        },
        {
            "month_year": "9-2019",
            "events": [
                {
                    "id": 32,
                    "title": "Event 4",
                    "description": "Event 4",
                    "date": "2019-09-04"
                },
                {
                    "id": 29,
                    "title": "Event 1",
                    "description": "Event 1",
                    "date": "2019-09-25"
                }
            ]
        }
    ]
}

it should be like this

{
    "count": 4,
    "next": null,
    "previous": null,
    "results": [
        {
            "month_year": "6-2019",
            "events": [
                {
                    "id": 30,
                    "title": "Event 2",
                    "description": "Event 2",
                    "date": "2019-06-02"
                },
                {
                    "id": 31,
                    "title": "Event 3",
                    "description": "Event 3",
                    "date": "2019-06-04"
                }
            ]
        },
        {
            "month_year": "9-2019",
            "events": [
                {
                    "id": 32,
                    "title": "Event 4",
                    "description": "Event 4",
                    "date": "2019-09-04"
                },
                {
                    "id": 29,
                    "title": "Event 1",
                    "description": "Event 1",
                    "date": "2019-09-25"
                }
            ]
        },

    ]
}
Thread Thread
 
lord007tn profile image
Raed Bahri •

check you data base from django admin maybe you duplicate the data !
there is no error in the code

Collapse
 
lord007tn profile image
Raed Bahri •

does it worked ??

Thread Thread
 
tipu profile image
Borhan Tipu •

No.

Collapse
 
tipu profile image
Borhan Tipu • • Edited

It's not working for me.


return queryset.objects.filter(data=id).order_by('-date')

where you get the data=id?


date = datetime.datetime.now()
return date.strftime("%m/%d/%Y")

I need the date from date field. Not from the datetime.now()

Collapse
 
lord007tn profile image
Raed Bahri •

okay the id is a field that django auto add in your model as a primary key you can made you custom primary key look in the documentation.

ah so you need to delete the datetime.now and just return the date forma from you model it should look like this

return instance.date.strftime("%m/%d/%Y")

change the date variable by you varibale name