Search code examples
djangodjango-querysetdjango-signals

Only show objects where the total amount of rows of a subquery is less than a certain value in django queryset


I'm trying to generate a list of a certain model where only the instances from another query. So Here's my models:

class Waiter(models.Model):
    id = models.AutoField(primary_key=True)
    waiter = models.CharField(max_length=200)


class Service(models.Model):
    id = models.AutoField(primary_key=True)
    arrival = models.DateTimeField(auto_now_add=True)
    exit = models.DateTimeField(null=True, blank=True)
    waiter = models.ForeignKey('Waiter', on_delete=models.CASCADE)
    article = models.ForeignKey('Table', on_delete=models.CASCADE)
    total_amount= models.DecimalField(max_digits=15, decimal_places=2)

class Table(models.Model):
    id = models.AutoField(primary_key=True)
    table_no = models.CharField(max_length=200)

The idea is there can only be four waiters assigned at a service at a single time. How can I filter the results of waiters based on weather they have less than four services at a single time?

Here's the signal I'm doing so far:

@reciever(pre_save, sender=Service)
def prepare_service(sender, instance, *args, **kwargs):
    if instance.id = None:
        time = datetime.now()
        # check if waiter has less than four services HERE
        # the following code is WRONG


        current_services = Service.objects.filter(exit__gt=time)
        available_waiters = Waiter.objects.select_related\
            (waiter__in=Subquery())
        #check if waiter has less than four services

Thank you in advance :)

edit: Okay, I think I might have gotten it right. Now I have an update to my question, given that I have already got count on a select related how do I filter on the counted model, so If I want to count only the services where the service ends after my arrival how would I do so on the count?

Here's my current line:

waiters = Waiter.objects.select_related('Service').annotate(num_Service=Count('Service', filter=Q(exit__gt=time)))

Solution

  • from django.db.models import Count
    
    q = Waiter.objects.select_related('Service').annotate(num_Service=Count('Service'))
    print Waiter[0].num_Service
    

    based on this answer