I have a Django queryset that ideally does some annotation and filtering with 3 object classes. I have Conversations, Tickets, and Interactions.
My desired output is Conversations that have 1. an OPEN ticket, and 2. exactly ONE interaction, of type mass_text, since the ticket's created_at date.
I am trying to annotate the conversation query with ticket_created_at
& filter out Nones, then somehow use that ticket_created_at
parameter in a subsequent annotation/subquery to get count of interactions since the ticket_created_at
date. Is this doable?
class Interaction(PolymorphicModel):
when = models.DateTimeField()
conversation = models.ForeignKey(Conversation)
mass_text = models.ForeignKey(MassText)
class Ticket(PolymorphicModel):
created_at = models.DateTimeField()
conversation = models.ForeignKey(Conversation)
status = models.CharField()
########################################################
open_ticket_subquery = (
Ticket.objects.filter(conversation=OuterRef("id"))
.filter(status=Ticket.Status.OPEN)
.order_by("-created_at")
)
filtered_conversations = (
self.get_queryset()
.select_related("student")
.annotate(
ticket_created_at=Subquery(
open_ticket_subquery.values("created_at")[:1]
)
)
.exclude(ticket_created_at=None)
.annotate(interactions_since_ticket=Count('interactions', filter=Q(interactions__when__gte=ticket_created_at)))
.filter(interactions_since_ticket=1)
This isn't working, because I can't figure out how to use ticket_created_at
in the subsequent annotation.