Annotate from related QuerySet

Using Django Using the ORM
1

Consider the following scenario (models)

class Service(models.Model):
    service_type = models.ForeignKey(
        ServiceType,
        related_name="typeservices"
        on_delete=models.CASCADE,
    )
    description = models.TextField()
    active = models.BooleanField(default=True)

class Booking(models.Model)
    service = models.ForeignKey(
        Service,
        related_name="bookings"
        on_delete=models.CASCADE,
    )
    category = models.CharField(max_length=255)
    price = models.IntegerField()
    client_serial = models.CharField(max_length=255)

class Client(models.Model):
    serial = models.CharField(max_length=255, unique=True)

Now I have a queryset of services. I would like to decorate (annotate) the queryset with extra columns representing the number of times services were booked – I would like that to happen for bookings of specific price and serial_serial. I anticipate that only one item will satisfy the query at most. So I did …

queryset.annotate(
    # number of occurrence
    occurs=models.Count(
        "bookings", 
        filter=models.Q(
            bookings__client_serial='1234ARG-QWE',
            bookings__price=45.00
        )
    )
)

That works.

Count() returns a single value (an aggregation). It suited the annotation since I wanted a unit value for the booking made by a specific client, for $45 – something that should happen only once. But I also want two more annotations on the queryset from Bookings. So I append the following to the above query …

.annotate(
    # booking category
    cat=models.F("bookings__category")
).annotate(
    booking_id=models.F("bookings__pk")
)

My observation is that I get duplicate entries. It pulls services that were not booked and decorates them. Without the last annotate() block, everything is fine. Why? How do I build this query such that the queryset does not grow to include irrelevant entries?

2

Please post the complete query being executed as a single statement, including the base queryset being executed.

3 
Services.objects.filter(active=True).annotate(
    # number of occurrence
    occurs=models.Count(
        "bookings", 
        filter=models.Q(
            bookings__client_serial='1234ARG-QWE',
            bookings__price=45.00
        )
    )
).annotate(
    # booking category
    cat=models.F("bookings__category")
).annotate(
    booking_id=models.F("bookings__pk")
)
4

Perhaps I should be asking for how to filter outputs from F() objects.

5

What are you looking to get as a result here? It may be more useful if you describe what it is you’re trying to obtain.

From what I can see here…

A query as Services.objects.filter(... is going to retrieve a queryset.

Each element (where active=True) is going to have an occurs attribute with a count of bookings related to that Service with the matching filter. So if you have 10 Services with active=True, I would expect the queryset to have 10 entries, with most of them having occurs=0.

I would also expect all of the Services to then also have the new attributes of cat and booking_id. Those annotations are not limited by the bookings annotation and so everything is going to “match”.

6

Actually, in my case, everything works as expected when I remove the annotation that performs field lookup (bookings__id). Assuming the base query (the filter) produces 10-rows. I expect all t0 to have three extra columns (the annotations, that is “occurs”, “cat” and “booking_id”). Without the lookup on “bookings__id”, I get 10-rows. Otherwise, I get more than 10