How to Reduce Queries while Querying/Filtering GenericRelated Field Object

Question

I have a Like model whose code looks like this:

class Like(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    liked_on = models.DateTimeField(auto_now_add=True)

    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey()

    class Meta:
        unique_together = ('customer', 'content_type', 'object_id')
        indexes = [
            models.Index(fields=["content_type", "object_id"]),
        ]

    def __str__(self):
        return f'{self.customer.user} likes {self.content_object}'

This generic relation enables me to reuse the likes model for the CampusAD and PropertyAD models which both have a GenericRelation to the Likes model.

In the PropertyADSerializer which feeds the endpoints, /api/properties and /api/properties/{property_id}, I have a SerializerMethodField which provides a boolean telling the front-end which properties have been liked by the current authenticated user (useful for showing a like icon on the page for users). The SerializerMethodField is defined as thus:

    def get_liked_by_authenticated_user(self, obj):
        request = self.context.get('request')
        if request and request.user.is_authenticated:
            customer = Customer.objects.get(user_id=request.user.id)
            content_type = ContentType.objects.get_for_model(PropertyAD)
            likes = Like.objects.filter(
                customer=customer, content_type=content_type,
                object_id=obj.id)
            return likes.exists()
        return False

Now, the issue with this approach is that it makes calls to the Customer and ContentType tables to fetch the required Customer and PropertyAD objects before using them for filtering, and this happens for each PropertyAD on the page resulting in hundreds of calls to the database as shown in the debug toolbar output below:

default 121.59 ms (147 queries including 141 similar and 71 duplicates )

    
SELECT ••• FROM `core_auth_customer` WHERE `core_auth_customer`.`user_id` = 1 LIMIT 21
71 similar queries. Duplicated 71 times.    
    1.29    
    
SELECT ••• FROM `properties_propertyad` WHERE `properties_propertyad`.`owner_id` = 1
    
    9.75    
    
SELECT ••• FROM `properties_propertyimage` WHERE `properties_propertyimage`.`ad_id` IN (9, 33, 58, 114, 118, 145, 149, 177, 185, 244, 248, 266, 271, 275, 281, 318, 331, 345, 349, 353, 354, 358, 381, 391, 395, 396, 406, 431, 435, 444, 453, 458, 491, 493, 518, 544, 548, 567, 594, 601, 652, 656, 671, 675, 704, 732, 746, 747, 777, 782, 808, 813, 821, 845, 893, 894, 896, 902, 908, 937, 950, 951, 957, 962, 969, 971, 973, 975, 1001, 1002)
    
    1.47    
    
SELECT ••• FROM `core_auth_customer` INNER JOIN `core_auth_user` ON (`core_auth_customer`.`user_id` = `core_auth_user`.`id`) WHERE `core_auth_customer`.`id` IN (1) ORDER BY `core_auth_user`.`first_name` ASC, `core_auth_user`.`last_name` ASC
    
    1.10    
    
SELECT ••• FROM `core_auth_customer` WHERE `core_auth_customer`.`user_id` = 1 LIMIT 21
71 similar queries. Duplicated 71 times.    
    1.05    
    
SELECT ••• FROM `likes_like` WHERE (`likes_like`.`content_type_id` = 6 AND `likes_like`.`customer_id` = 1 AND `likes_like`.`object_id` = 9) LIMIT 1
70 similar queries.     
    0.94    
    
SELECT ••• FROM `core_auth_customer` WHERE `core_auth_customer`.`user_id` = 1 LIMIT 21
71 similar queries. Duplicated 71 times.    
    0.93    
    
SELECT ••• FROM `likes_like` WHERE (`likes_like`.`content_type_id` = 6 AND `likes_like`.`customer_id` = 1 AND `likes_like`.`object_id` = 33) LIMIT 1
70 similar queries.     
    0.54    
    
SELECT ••• FROM `core_auth_customer` WHERE `core_auth_customer`.`user_id` = 1 LIMIT 21
71 similar queries. Duplicated 71 times. 


...

I have tried prefetching the Customer and PropertyAD objects as thus :

    def get_liked_by_authenticated_user(self, obj):
        request = self.context.get('request')
        if request and request.user.is_authenticated:
            customer = Customer.objects.get(user_id=request.user.id)
            content_type = ContentType.objects.get_for_model(PropertyAD)
            likes = Like.objects.prefetch_related('customer', 'content_type').filter(
                customer=customer, content_type=content_type,
                object_id=obj.id)
            return likes.exists()
        return False    def get_liked_by_authenticated_user(self, obj):
        request = self.context.get('request')
        if request and request.user.is_authenticated:
            customer = Customer.objects.get(user_id=request.user.id)
            content_type = ContentType.objects.get_for_model(PropertyAD)
            likes = Like.objects.prefetch_related('customer', 'content_type').filter(
                customer=customer, content_type=content_type,
                object_id=obj.id)
            return likes.exists()
        return False

...but that did not seem to make any difference to the queries. I also thought of caching the returned objects, but that does not seem like an ideal solution to the problem as they may change relatively often.

Are there any optimization techniques for filtering GenericRelations I may be unaware of, or is there a totally different way I can filter the likes without having to incur all those extra database calls? I've tried many iterations and they aren't working. Or... is there another way I should be supplying the information about what property has been liked by the authenticated user, to the front-end?

How to Reduce Queries while Querying/Filtering GenericRelated Field Object

0 Answers0