Django self join with ForeignKey (ORM)-CodePudding

I have an Event Model:

class Event(models.Model):
    message = models.CharField(max_length=128)
    timestamp = models.DateTimeField()
    cancels_event = models.ForeignKey('self', on_delete=models.CASCADE)

I'm analysing lots of event messages, but firstly I need to check if the event has been canceled.

Let's say I receive an Event_1, then I receive an Event_2 with Event_2.cancels_event=Event_1, so Event_2 cancels Event_1.

I want to find out, given a subset of Events, which Events from this subset have been canceled.

In SQL I'd use a join with self:

SELECT * FROM Event e1
JOIN Event e2
ON e1.id = e2.cancels_event

But I don't know how to do it within Django ORM.

CodePudding user response：

You can filter with:

Event.objects.filter(
    event__isnull=False
)

or if you want to prevent returning the same Event multiple times, you can add .distinct():

Event.objects.filter(
    event__isnull=False
).distinct()

This works because the default related_query_name=… value ^[Django-doc] for your cancels_event is event. You can rename this to canceling_events for example:

class Event(models.Model):
    # …
    cancels_event = models.ForeignKey(
        'self',
        related_name='canceling_events'
        on_delete=models.CASCADE
    )

Then the query is:

Event.objects.filter(
    canceling_events__isnull=False
)

CodePudding user response：

This code doesn't use join but works.

canceled_list= Event.objects.all().values_list('cancels_event')

result = Event.objects.filter(pk__in = canceled_list)