How to "filter" by "exists" in Django?

Question

I would like to filter a queryset by whether a certain subquery returns any results. In SQL this might look like this:

SELECT * FROM events e WHERE EXISTS
    (SELECT * FROM tags t WHERE t.event_id = e.id AND t.text IN ("abc", "def"))

In other words, retrieve all events which are tagged with one of the specified tags.

How might I express this using Django's QuerySet API on models Event and Tag?

Answer 1

You can do something like this:

q = Event.objects.filter(tag__text__in = ['abc', 'def'])

Assuming that there is a ForeignKey from Tag to Event.

Explanation: You are filtering Event objects based on a specific criteria. Using the double underscore syntax you are accessing the text attribute of the Tag instances and then attaching the IN condition. You don't have to worry about the join on foreign key; Django does that for you behind the scenes. In case you are curious to see the query generated, you can print it:

print q.query

Answer 2

Manoj's solution may cause a problem when there are multiple tags for an event.

The SQL inner join returns all the rows so events may have duplicate results, the solution is to add the distinct method.

q = Event.objects.filter(tag__text__in = ['abc', 'def']).distinct()