CODEWITHSUNDEEP
djangodjango-models
AngelQuesada
AngelQuesada

Reputation: 431

DJANGO - how can I filter an object filtering by another object filtered

I'm trying to make an advanced filter with django objects but I just have no clue of how to do it. I'll try to explain myself:

I have 2 objects:

Consumptions:

class Consumption(LogsMixin, models.Model):
    """Definición del modelo de Consumos"""

    STATES = [
        ('not-prevalidated', 'No prevalidado'),
        ('prevalidated', 'Prevalidado'),
        ('pendant', 'Pendiente validación cliente'),
        ('accepted', 'Aceptado'),
    ]

    client = models.ForeignKey('authentication.Client', verbose_name=("Cliente"), null=True, default=None, on_delete=models.SET_DEFAULT)
    access_date = models.DateField("Fecha de acceso", auto_now=False, auto_now_add=False)
    status = models.CharField("Estado", max_length=20, choices=STATES, null=False, blank=False)

and Clients

class Client(LogsMixin, models.Model):
    """Model definition for Client."""

company_name = models.CharField("Nombre de la empresa", max_length=150, default="Nombre de empresa", null=False, blank=False)
user = models.OneToOneField(User, null=True, on_delete=models.CASCADE)
dateadded = models.DateTimeField("Fecha de inserción", default=datetime.datetime.now)

And now I want to count all clients that has some consumption in 'pendant' state and in certain date. As you can see consumptions has one client related.

I've checked the docs https://docs.djangoproject.com/en/3.1/topics/db/queries/ but I just can't get what I want.

Could someone help me? :(

Upvotes: 1

Views: 252

Answers (4)

Wasiq W
Wasiq W

Reputation: 36

What ended up fixing OP's problem was a distinct() call to avoid any duplicate rows from being selected. Fair reminder though that in most cases, a call for distinct() isn't required. It's just that when queries span over multiple tables there's a chance for a row to be selected twice.

[read comments]

client_count = Client.objects.filter(
consumption__status="pendant",
consumption__access_date="2020-11-03").distinct().count()

When you say "I want to count the clients that has consumptions in certain state" what exactly do you mean? Can you provide more detail as to what exactly you are expecting?

The other answer with the following code will indeed count the number of clients that have consumptions in a certain state.

client_count = Client.objects.filter(
    consumption__status="pendant",
    consumption__access_date="2020-11-03").count()

But it will not count each client twice if the client has multiple 'consumptions', is that what you wish? If that's the case then perhaps filtering the consumptions is a better idea?

count = Consumption.objects.filter(
    status="pendant",
    access_date="2020-11-03").count()

This will give you a count of the total consumptions in that state, and if you wish to access all the clients then simply access consumption.client.

Upvotes: 1

elesire
elesire

Reputation: 11

Could this do the job:

nb_clients = Client.objects.filter(consumption_set__status='pendant',consumption_set__access_date__range=(start_date, end_date)).count()

doc backward relationship : https://docs.djangoproject.com/en/3.1/topics/db/queries/#following-relationships-backward

doc range : https://docs.djangoproject.com/en/3.1/ref/models/querysets/#range

Upvotes: 1

Pruthvi Barot
Pruthvi Barot

Reputation: 2018

you can try this way:

from django.db.models import Count

client_count = Consumption.objects.filter(status='pendant',access_date=some_date).aggregate(client_count = Count('client'))['client_count']

Upvotes: 1

JPG
JPG

Reputation: 88499

lookup through the span relation as,

client_count = Client.objects.filter(
    consumption__status="pendant",
    consumption__access_date="2020-11-03").count()

Upvotes: 1

Related Questions