0

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.

ezeYaniv
  • 412
  • 5
  • 18

0 Answers0