Django - Database querying - Checking if a list of values is a subset of another list

Question

I'm programming a website aimed at teaching a language. The idea is that by validating lessons, the students are unlocking other contents (exercises, songs...). Formally, at each lesson are attached tags. Whenever students validate a lesson, they validate the associated tags. Each content is flagged with prerequisites corresponding to those tags.

On a page, I want to display all the songs users can get access to based on the tags they unlocked. If they unlocked all the tags associated with the song, they can view it; otherwise they can't.

Here is the model of a lesson (called cours) :

class Cours(models.Model):
    niveau=models.ForeignKey(Niveau,on_delete=models.CASCADE)
    titre=models.CharField(max_length=255, unique=True)
    tags=models.ManyToManyField(Tag)

Here is the model of a song :

class Chanson(models.Model):
    titre=models.CharField(max_length=255, unique=True)
    prerequis=models.ManyToManyField(Tag,blank=True,related_name="+")

Here is the model of the profile of a user and the solution I found out to answer my problem using Python built-in sets.

class Profil(models.Model):
    user=models.OneToOneField(User, on_delete=models.CASCADE, primary_key=True)
    cours_valides=models.ManyToManyField(Cours)
    chanson_valides=models.ManyToManyField(Chanson)

    def songs_to_do(self):
        songlist=Chanson.objects.exclude(id__in=self.chanson_valides.values_list('id',flat=True))
        outputsonglist=list()
        for song in songlist:
            if set(song.prerequis.values_list('id',flat=True))<=set(self.cours_valides.values_list('tags',flat=True)):
                outputsonglist.append(song)
        return outputsonglist

The songs to do method basically returns the list of songs users have not covered yet but can access based on the lessons they validated so far. Indeed, the cours valides field lists all the lessons validated by users while the chansons valides field lists all the songs already covered by the users.

I wanted to know if there is a more efficient way to treat this kind of problem ?

Answer 1

I would perform 2 queries:

Get all the tags the users does not own:

locked_tags = Tag.objects.exclude(cours__profil=self)

Find all the songs which have a tag the user has not unlocked yet:

unlocked_songs = Chanson.objects.exclude(prerequis__in=locked_tags)

Your code would look like this:

class Profil(models.Model):
    user=models.OneToOneField(User, on_delete=models.CASCADE, primary_key=True)
    cours_valides=models.ManyToManyField(Cours)
    chanson_valides=models.ManyToManyField(Chanson)

    def unlocked_songs(self):
        locked_tags = Tag.objects.exclude(cours__profil=self)
        return Chanson.objects.exclude(prerequis__in=locked_tags)