How can I count the fields of All Objects in a Database Table by date in Django?

Using Django
1

Hi Django Experts,
How can I count the fields of All Objects in a Database Table by date in Django?

For example: I can access the number of all records of my model named “MateryalEkle” with this code below {{materyalIstatistik}}.

But I want to reach the count of all fields separately. And I want to do them by date range i.e. weekly, monthly and yearly.

for example: how many “TELEFON”, how many “SIM KART”, how many “SD KART”.

and I want to filter these count by date range.

for example : 01.03.2021 - 07.03.2021 or 01.01.2021 -.01.01.2022

How can I do that? Thanks for your help.

models.py

class MateryalEkle(models.Model):
   
    MATERYALCINSI = [
    ('TELEFON', 'TELEFON'),
    ('SIM KART', 'SIM KART'),
    ('SD KART', 'SD KART'),
    ('USB BELLEK', 'USB BELLEK'),
]

    materyalMarka = models.CharField(max_length=50, verbose_name='MATERYAL MARKA', blank=True, null=True, default="-")
    cinsi = models.CharField(max_length=50, choices=MATERYALCINSI, verbose_name='MATERYAL CİNSİ', blank=True, null=True, default="-")
    gelisTarihi = models.DateTimeField(auto_now_add=True)
    slug = AutoSlugField(populate_from='materyalMarka', unique=True, blank=True, null=True)

    def __str__(self):
        return self.materyalMarka

views.py

def istatistik(request):
    materyalIstatistik = MateryalEkle.objects.all().count()
    
    return render(request, 'delil/istatistikler.html', {'materyalIstatistik':materyalIstatistik})

istatistikler.html

<p>materyal : {{materyalIstatistik}} </p>

2

I’m not clear on what you’re looking for.

Are you looking for a query that when given a type (such as ‘TELEFON’ or ‘SIM KART’) and a date range, that it gives you a count of the number of rows that satisfy that condition?

Or are you looking for a query that will return multiple rows, with each row being a combination of a type and a date range? (So you might have rows for each of TELEFON and Jan, Feb, Mar, and SIM KART for Jan, Feb, Mar, etc?)

Or are you looking for the counts of a single type, but broken down by date range?

Each one of these are going to have different answers.

1 Like
3

I want to do exactly what I did here.
Thank you for your help.

1205×766 24 KB

4

If you’re looking to filter on a specific range and count the instances within that range you can use the following:

aggregates = MateryalEkle.objects.filter(gelisTarihi__range=DATE_RANGE).aggregate(
    telefon_count=Count('id', filter=Q(cinsi='TELEFON')),
    sim_kart_count=Count('id', filter=Q(cinsi='SIM KART')),
    ...
)

If want to return this per date range, that’s a little more tricky. I suspect you’d want to use a Window expression.

1 Like
5

Rather than using .aggregate() with all of the items listed, you could generate that query with:

MateryalEkle.objects.filter(
    gelisTarihi__range=DATE_RANGE,
).values('cinsi').annotate(count=Count('id'))

Then each instance returned would have two properties. cinsi and count. You could even convert it read it into a dict so you end up with a map.

1 Like
6

12
12860×192 11 KB

Thanks for your interest, how can I fix this and show it in a template?

7

DATE_RANGE is the range of dates you want to run the query on. You’ll need to define that.

I should have mentioned that Q is imported via from django.db.models import Q

1 Like
8

I could do something like this. But I still can’t show the count.

views.py

def istatistik(request):
       
    tumMateryalBilgileri = MateryalEkle.objects.all()

    filtre = MateryalFiltrele(request.GET, queryset=tumMateryalBilgileri)

    tumMateryalBilgileri = filtre.qs

    return render(request, 'delil/istatistikler.html', {'filtre':filtre})

filters.py

class MateryalFiltrele(django_filters.FilterSet):
    baslangicTarihi = DateFilter(field_name="eklenmeTarihi", lookup_expr="gte")
    bitisTarihi = DateFilter(field_name="eklenmeTarihi", lookup_expr="lte")
    class Meta:
        model = MateryalEkle
        fields = []

istatistikler.html

        <form method="get">

            {{ filtre.form.baslangicTarihi |as_crispy_field }}

            {{ filtre.form.bitisTarihi|as_crispy_field }}

            <input type="submit" />

        </form>

        
        {% for tekil in filtre.qs %}

        <p>{{ tekil.cinsi }}</p>

        {% endfor %}

screen:

ewr
ewr822×565 6.01 KB

So this is exactly what I want it to happen: i can’t get this view. As I am new to Django, I can only do so if you write the whole example while telling me. Thank you from now.

234

9

I did some things but still haven’t reached my goal.

10

I don’t think you can calculate the variety of values you’re looking to generate from one query - or even a small number of queries. I don’t know how many rows you’re talking about in total here, but if I had to do this, I would retrieve all rows, and then figure out which category each row fits in to.

11

This is how I solved the problem I was experiencing. I am sharing it here with the thought that it will help. Thank you to everyone who helped!

filters.py

class MateryalFiltrele(django_filters.FilterSet):

    baslangicTarihi = DateFilter(field_name="eklenmeTarihi", lookup_expr="gte", label='')

    bitisTarihi = DateFilter(field_name="eklenmeTarihi", lookup_expr="lte", label='')

    class Meta:

        model = MateryalEkle

        fields = ('cinsi',)

views.py

def istatistik(request):     

    tumMateryalBilgileri = MateryalEkle.objects.values('cinsi').annotate(Count('cinsi'))

    filtre = MateryalFiltrele(request.GET, queryset=tumMateryalBilgileri)

    return render(request, 'delil/istatistikler.html', {'filtre':filtre})

istatistikler.html

<table class="table mt-5">
                <thead>
                  <tr>
                    {% for tekil in filtre.qs %}
                    <th class="ubold text-info " scope="col">{{tekil.cinsi}}</th>
                    {% endfor %}
                  </tr>
                </thead>
                <tbody>
                    <tr>
                    {% for tekil in filtre.qs %}
                    <td>{{tekil.cinsi__count}}</td>
                    {% endfor %}
                  </tr>
                    </tbody>
              </table>

fasdf
fasdf1453×213 2.85 KB