Django query based on through table

Question

I have a 4 models which are Contents, Filters,ContentFilter , Users.

a user can view contents. a content can be restricted using Filters so users can't see it.

here are the models.

class Content(models.Model):
    title = models.CharField(max_length=120)
    text = models.TextField()
    filters = models.ManyToManyField(to="Filter", verbose_name=_('filter'), blank=True, related_name="filtered_content",through='ContentFilter')

class Filter(models.Model):
    name = models.CharField(max_length=255, verbose_name=_('name'), unique=True)
    added_user = models.ManyToManyField(to=User, related_name="added_user", blank=True)
    ignored_user = models.ManyToManyField(to=User, related_name="ignored_user", blank=True)
    charge_status = models.BooleanField(blank=True, verbose_name=_('charge status'))

class ContentFilter(models.Model):
    content = models.ForeignKey(Content, on_delete=models.CASCADE)
    filter = models.ForeignKey(Filter, on_delete=models.CASCADE)
    manual_order = models.IntegerField(verbose_name=_('manual order'), default=0,rst'))
    access = models.BooleanField(_('has access'))

What it means is that 5 contents exist(1,2,3,4,5).

2 users exist. x,y

A filter can be created with ignored user of (x).

Contents of 1,2,3 have a relation with filter x.

so now X sees 4,5 and Y sees 1,2,3,4,5

---

what I'm doing now is that based on which user has requested find which filters are related to them. then query the through table(ContentFilter) to find what contents a user can't see and then exclude them from all of the contents.(this helps with large joins)

filters = Filter.objects.filter(Q(added_user=user)|(Q(ignored_user=user))
excluded_contents = list(ContentFilter.objects.filter(filter__in=filters).values_list('id',flat=True))
contents = Contents.objects.exclude(id__in=excluded_contents)

---

Problem

I want a way so that Filters can have an order and filter a queryset based on top ContentFilter for each user. for example content 1 can be blocked for all users with 1 filter ( filter x where ignored user has all the users) but in ContentFilter has a manual_order of 0.

then in a second filter all users who have a charge status of True can see this content.(filter y where added user has all the users and charge status True.) and in ContentFilter has a manual_order of 1.

I think I can do it using a for loop to check all the contents and choose the top most ContentFilter of them based on filters that include that user but it's both time and resource consuming.

and I prefer not to use raw SQL but I'm not sure if there is a way to do it using django orm

Answer 1

I managed to solve this using Subquery.

first I create a list of filters that user is part of.

filters = Filter.objects.filter(Q(added_user=user)|(Q(ignored_user=user))

then I create a subquery to assign each content with a access value (if any filter is applied on it.)

current_used_filters = ContentFilter.objects.filter(Q(filter__in=user_filters),content=OuterRef('pk')).order_by('-manual_order')
blocked_content_list = Content.objects.annotate(access=Subquery(current_used_filters.values('access')[:1])).filter(
        access=False).values_list('id', flat=True)

this raises a problem if any of my contents does not have a filter of filters associated with it then it would not be included in this. so I filter the ones that have an access value of False this means that this content has a filter with a high manual order which blocks it for this specific user. so now I have a list of content IDs which now I can exclude from all contents. so it would be:

contents = Contents.objects.exclude(id__in=blocked_content_list)