Django Filter ManyToManyField with particular clause

Question

I'm under Django 1.8. I have several playlists that contain each several videos. some of these videos have a status of deleted or error.

I want only playlists that contain video with a status of "online".

class Video(models.Model):  

    # Title of the video
    title = models.CharField(max_length=100)


    # Status of video (error, online, deleted)
    status = models.CharField(max_length=20, default="online")


class UserPlaylist(models.Model):  

    # Name of the playlist
    name = models.CharField(max_length=500, blank=True, null=True)

    # Playlist owner
    owner = models.ForeignKey(User)

    # videos
    videos = models.ManyToManyField(Video, null=False)

What I tryed with no success:

UserPlaylist.objects.filter(videos__status="online")
UserPlaylist.objects.filter(videos__status="online").distinct()

Works but painfull:

UserPlaylist.objects.exclude(videos__status='error').exclude(videos__status='deleted').distinct()

Answer 1

We can count the number of Videos with status='online', and check if that is the same as the number of total videos, like:

from django.db.models import Case, F, Q, When

UserPlaylist.objects.annotate(
    nvideo=Count('videos'),
    nonline=Count(Case(
        When(videos__status='online', then='videos'),
        default=None
    ))
).filter(
    nvideo=F('nonline')
)

This will yield a query that looks like:

SELECT userplaylist.id, userplaylist.name,
       COUNT(userplaylist_videos.video_id) AS nvideo,
       COUNT(CASE WHEN video.status = online
             THEN userplaylist_videos.video_id
             ELSE NULL END) AS nonline
FROM userplaylist
LEFT OUTER JOIN userplaylist_videos
             ON userplaylist.id = userplaylist_videos.userplaylist_id
LEFT OUTER JOIN video ON userplaylist_videos.video_id = video.id
GROUP BY userplaylist.id
HAVING COUNT(userplaylist_videos.video_id) = COUNT(
    CASE WHEN video.status = online
    THEN userplaylist_videos.video_id
    ELSE NULL END)

Note that a UserPlaylist that contains no videos will be part of the result as well, since all its videos are online.