[SOLVED] Django reverse foreign key leading to duplicate queries

Django reverse foreign key leading to duplicate queries

I'm trying to use ViewSet to return list of all assets with the asset type name (instead of just id) but according to django-debug-toolbar, my queries are being duplicated leading to slower results.

1 Asset type can have multiple assets.

So, when I try to retrieve all assets (children) -- it's trying to fetch the asset type (parent) names for each of the assets (children) but it's running one query for every asset (child). Looks something like this: It's duplicated 6 times because I have 6 values in the Asset table currently.

QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.00    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.        3.95    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.

Expected Output

All I want is just a list of all the Assets along with the AssetType names -- is there a better way to do this?

Here are my models:

class AssetType(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=80, unique=True)
    description = models.CharField(max_length=80)
    
    class Meta:
        db_table = "mm_asset_type"

class Asset(models.Model):
    asset_type = models.ForeignKey(AssetType, on_delete=models.CASCADE)
    asset_name = models.CharField(max_length=80)
    display_name = models.CharField(max_length=80)

    class Meta:
        db_table = "mm_asset_registry"

Here are my serializers:

class AssetTypeSerializer(serializers.ModelSerializer):
    class Meta:
        model = AssetType
        fields = "__all__"


class AssetSerializer(serializers.ModelSerializer):
    asset_type_name = serializers.CharField(source='asset_type.name')
    
    class Meta:
        model = Asset
        fields = ("id", "asset_type_name", "asset_name", "display_name")

Finally, this is my ViewSet

class AssetViewSet(mixins.ListModelMixin,viewsets.GenericViewSet):
    queryset = Asset.objects.all()
    serializer_class = AssetSerializer

So, when I try to retrieve all assets -- it's trying to fetch the asset type names for each of the assets but it's running one query for every asset. Looks something like this:

QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (27,)
 6 similar queries.  Duplicated 3 times.        3.00    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.        3.95    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.        3.99    
Sel Expl
+   
QUERY = 'SELECT TOP 21 [mm_asset_type].[id], [mm_asset_type].[name], [mm_asset_type].[description] FROM [mm_asset_type] WHERE [mm_asset_type].[id] = %s' - PARAMS = (29,)
 6 similar queries.  Duplicated 3 times.

Expected Output

All I want is just a list of all the Assets (children) along with the AssetType (parent) names -- is there a better way to do this?

Thanks

Solution

You should use .select_related(…) [Django-doc] to fetch the types with the same query:

class AssetViewSet(mixins.ListModelMixin,viewsets.GenericViewSet):
    queryset = Asset.objects.select_related('asset_type')
    serializer_class = AssetSerializer

This will thus also fetch the asset_type fields in the same query as the one to fetch the Assets, and thus reduces the total number of queries to one.