Django | How to make a reverse Relationship with two models

Question

i want to make a ticket system. I have the main model (TicketSystem) and a model with the messages from the users (TicketSystem_Messages).

In the model "TicketSystem_Messages" is the ForeignKey to the model "TicketSystem".

Here is my code:

class TicketSystem(models.Model):
    subject = models.CharField(_('Subject'),max_length=30, blank=False, default="N/A")
    message = models.TextField(_('Message'), null=False, blank=False)
    created_date = models.DateTimeField(_('Date'), default=datetime.utcnow().replace(tzinfo=utc))
    fertig_date = models.DateTimeField(_('Date'), default=datetime.utcnow().replace(tzinfo=utc))

class TicketSystem_Messages(models.Model):
    user_name = models.ForeignKey(User)
    status = models.ForeignKey(TicketSystem_Status)
    ticketid = models.ForeignKey(TicketSystem)
    message = models.TextField(_('Message'), null=False, blank=False)
    created_date = models.DateTimeField(_('Sent'), default=datetime.utcnow().replace(tzinfo=utc))

At the moment i get the Tickets without the Messages:

sql_TicketSystem = TicketSystem.objects.filter(id=kwargs['pk'])

I want to make a LEFT JOIN like this

SELECT * FROM TicketSystem LEFT JOIN TicketSystem_Messages ON Ticketsystem.id = TicketSystem_Messages.ticketid

I heard something about "select_related" and "prefetch_related" and tried it but it does not work.

Answer 1

Rather than using raw SQL joins, in Django you can traverse model relationships in either direction, regardless of how they are represented in the database.

To find all the messages for a given TicketSystem instance:

my_ticket = TicketSystem.objects.get(id=0) # or something
my_ticket_messages = my_ticket.ticketsystem_messages_set.all() # or do other filters here

To find all the messages using a queryset:

TicketSystem_Messages.objects.filter(ticketId=my_ticket)

To find all tickets with more than one message:

from django.db import Count
TicketSystem.objects.annotate(message_count=Count('ticketsystem_messagess')).filter(message_count__gt=1)