Filtering nested models using generic foreign keys in django

Question

class Complaint(models.Model):
    complaint_id = models.UUIDField(default=uuid.uuid4, editable=False, unique=True, primary_key=True)
    user_type = models.ForeignKey(ContentType, on_delete=models.CASCADE, related_name='complaint_user')
    user_id = models.PositiveIntegerField()
    user_object = GenericForeignKey('user_type', 'user_id')
    complaint_date = models.DateTimeField(auto_now_add=True)
    complaint_time = models.TimeField(auto_now_add=True)
    complaint_message = models.CharField(max_length=1000, editable=True)
    type_of_complaint = models.CharField(editable=True, default='public')
    status = models.CharField(editable=True, default='pending')
    complaint_title = models.CharField(max_length=100, editable=True)
    
    # Should be able to assign to multiple models such as Counsellor, ClassTeacher, HOD
    content_type = models.ForeignKey(to=ContentType, on_delete=models.CASCADE, related_name='complaint_owner')
    object_id = models.PositiveIntegerField()
    assigned_to = GenericForeignKey('content_type', 'object_id')
    
    escalated_to = models.ForeignKey(to=EscalationStructure, on_delete=models.CASCADE)

I am trying to access a field in the user_object model, the is nested as user_object.user.username. I am accessing the username in this search vector

def search(request):
    query = request.GET.get('query', '')
    vector = SearchVector('complaint_title', 'complaint_message', 'status',
                          'complaint_time', 'complaint_date', 'counsellor_complaints', 'user_object__user__username')
    results = Complaint.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.001).order_by('-rank')
    return render(request, 'complaint_administration/view_complaints.html', {'complaints': results})

It throws an error saying Field 'user_object' does not generate an automatic reverse relation and therefore cannot be used for reverse querying. If it is a GenericForeignKey, consider adding a GenericRelation.

I'm not accessing a reverserelation right. I am accessing through the complaint it self ??

Answer 1

The error you're encountering is because Django's SearchVector and SearchRank functions do not support traversing relationships through GenericForeignKey. They are designed to work with regular foreign key relationships and fields on the model itself. In your case, since user_object is a GenericForeignKey, you cannot directly include user_object__user__username in the SearchVector because it involves traversing multiple relationships.

One solution is to use a Prefetch object to prefetch the related user object along with the complaints queryset, and then annotate the queryset with the user's username.

#search
from django.db.models import Prefetch, F

def search(request):
    query = request.GET.get('query', '')
    
    # Prefetch the related user object
    complaints_with_user = Complaint.objects.prefetch_related('user_object__user')
    
    # Annotate the queryset with the user's username
    complaints_with_username = complaints_with_user.annotate(
        user_username=F('user_object__user__username')
    )
    
    vector = SearchVector('complaint_title', 'complaint_message', 'status',
                          'complaint_time', 'complaint_date', 'user_username')
    
    results = complaints_with_username.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.001).order_by('-rank')
    
    return render(request, 'complaint_administration/view_complaints.html', {'complaints': results})

Another approach would be to create a denormalized field in your Complaint model to store the username directly.

#models.py
class Complaint(models.Model):
    ...
    # field to store the username directly for full-text search
    user_username = models.CharField(max_length=150, blank=True, null=True)

    def save(self, *args, **kwargs):
        if self.user_object:
            self.user_username = self.user_object.user.username
        super().save(*args, **kwargs)

and the search:

def search(request):
    query = request.GET.get('query', '')
    vector = SearchVector(..., 'assigned_to__user_username')
    results = Complaint.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.001).order_by('-rank')
    return render(request, 'complaint_administration/view_complaints.html', {'complaints': results})