I have an endpoint that will display results from a model. There's going to be a image illustrating each result, and if none of the posts of this category has a media associated, I'll get the writers avatar to display it.
I can get the media url value using the .values('media_source') method in the subqueries, but the problem is that the serializer expects a complex model with other fields and properties.
I've tried to use the code below to in order to get the instances, but I receive the following error:
Cannot resolve expression type, unknown output_field
I understand that I'd need to specify a field type (such as CharField, etc) but I can't figure out if there's a type that is actually interpreted as a Django model instance.
def get_queryset(self):
annotation = Coalesce(
Subquery(PostMedia.objects.filter(categories=OuterRef('pk'))), Subquery(UserMedia.objects.filter(posts__categories=OuterRef('pk')))
)
return Categories.objects.filter(is_public=True).annotate(media_source=annotation)
Here are the serializers:
class MediaSerializer(serializers.Serializer):
media_detail = MediaField(source="cloudinary_resource")
url = serializers.CharField(source="media_source")
type = serializers.CharField()
height = serializers.SerializerMethodField()
width = serializers.SerializerMethodField()
@staticmethod
def get_height(obj):
try:
return obj.metadata.get("height", None)
except AttributeError:
if isinstance(obj, dict):
try:
return obj['metadata'].get("height", None)
except KeyError:
pass
return
@staticmethod
def get_width(obj):
try:
return obj.metadata.get("width", None)
except AttributeError:
if isinstance(obj, dict):
try:
return obj['metadata'].get("width", None)
except KeyError:
pass
return
class CategorySerializer(serializers.ModelSerializer):
media = MediaSerializer(source='media_source', many=True)
tags = SimpleTagSerializer(many=True)
class Meta:
model = Category
fields = ("id", "name", "text", "media", "segment", "tags", "is_public", "is_validated", "metadata")
And the whole traceback:
api_1 | Internal Server Error: /api/v1/categories/
api_1 | Traceback (most recent call last):
api_1 | File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 56, in inner
api_1 | response = get_response(request)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response
api_1 | response = wrapped_callback(request, *callback_args, **callback_kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/views/decorators/csrf.py", line 55, in wrapped_view
api_1 | return view_func(*args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/viewsets.py", line 125, in view
api_1 | return self.dispatch(request, *args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 509, in dispatch
api_1 | response = self.handle_exception(exc)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 469, in handle_exception
api_1 | self.raise_uncaught_exception(exc)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
api_1 | raise exc
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 506, in dispatch
api_1 | response = handler(request, *args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/mixins.py", line 40, in list
api_1 | page = self.paginate_queryset(queryset)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/generics.py", line 171, in paginate_queryset
api_1 | return self.paginator.paginate_queryset(queryset, self.request, view=self)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/pagination.py", line 387, in paginate_queryset
api_1 | self.count = self.get_count(queryset)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/pagination.py", line 525, in get_count
api_1 | return queryset.count()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 470, in count
api_1 | return self.query.get_count(using=self.db)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/query.py", line 552, in get_count
api_1 | number = obj.get_aggregation(using, ["__count"])["__count"]
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/query.py", line 537, in get_aggregation
api_1 | result = compiler.execute_sql(SINGLE)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1348, in execute_sql
api_1 | sql, params = self.as_sql()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1858, in as_sql
api_1 | inner_query_sql, inner_query_params = self.query.inner_query.get_compiler(
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 573, in as_sql
api_1 | extra_select, order_by, group_by = self.pre_sql_setup()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 64, in pre_sql_setup
api_1 | self.setup_query()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 55, in setup_query
api_1 | self.select, self.klass_info, self.annotation_col_map = self.get_select()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 295, in get_select
api_1 | sql, params = col.select_format(self, sql, params)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/expressions.py", line 419, in select_format
api_1 | if hasattr(self.output_field, "select_format"):
api_1 | File "/usr/local/lib/python3.10/site-packages/django/utils/functional.py", line 49, in __get__
api_1 | res = instance.__dict__[self.name] = self.func(instance)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/expressions.py", line 283, in output_field
api_1 | raise FieldError("Cannot resolve expression type, unknown output_field")
api_1 | django.core.exceptions.FieldError: Cannot resolve expression type, unknown output_field
api_1 | Internal Server Error: /api/v1/categories/
api_1 | Traceback (most recent call last):
api_1 | File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 56, in inner
api_1 | response = get_response(request)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response
api_1 | response = wrapped_callback(request, *callback_args, **callback_kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/views/decorators/csrf.py", line 55, in wrapped_view
api_1 | return view_func(*args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/viewsets.py", line 125, in view
api_1 | return self.dispatch(request, *args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 509, in dispatch
api_1 | response = self.handle_exception(exc)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 469, in handle_exception
api_1 | self.raise_uncaught_exception(exc)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
api_1 | raise exc
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/views.py", line 506, in dispatch
api_1 | response = handler(request, *args, **kwargs)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/mixins.py", line 40, in list
api_1 | page = self.paginate_queryset(queryset)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/generics.py", line 171, in paginate_queryset
api_1 | return self.paginator.paginate_queryset(queryset, self.request, view=self)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/pagination.py", line 387, in paginate_queryset
api_1 | self.count = self.get_count(queryset)
api_1 | File "/usr/local/lib/python3.10/site-packages/rest_framework/pagination.py", line 525, in get_count
api_1 | return queryset.count()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/query.py", line 470, in count
api_1 | return self.query.get_count(using=self.db)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/query.py", line 552, in get_count
api_1 | number = obj.get_aggregation(using, ["__count"])["__count"]
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/query.py", line 537, in get_aggregation
api_1 | result = compiler.execute_sql(SINGLE)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1348, in execute_sql
api_1 | sql, params = self.as_sql()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1858, in as_sql
api_1 | inner_query_sql, inner_query_params = self.query.inner_query.get_compiler(
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 573, in as_sql
api_1 | extra_select, order_by, group_by = self.pre_sql_setup()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 64, in pre_sql_setup
api_1 | self.setup_query()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 55, in setup_query
api_1 | self.select, self.klass_info, self.annotation_col_map = self.get_select()
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 295, in get_select
api_1 | sql, params = col.select_format(self, sql, params)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/expressions.py", line 419, in select_format
api_1 | if hasattr(self.output_field, "select_format"):
api_1 | File "/usr/local/lib/python3.10/site-packages/django/utils/functional.py", line 49, in __get__
api_1 | res = instance.__dict__[self.name] = self.func(instance)
api_1 | File "/usr/local/lib/python3.10/site-packages/django/db/models/expressions.py", line 283, in output_field
api_1 | raise FieldError("Cannot resolve expression type, unknown output_field")
api_1 | django.core.exceptions.FieldError: Cannot resolve expression type, unknown output_field
api_1 | [21/Jun/2023 21:33:59] "GET /api/v1/categories/ HTTP/1.1" 500 165520
I've find a way to do what I need:
Create a custom Subquery class that transforms the whole Row of the table to JSONB format.
class ToJSONB(models.Subquery):
template = '(SELECT to_jsonb("row") FROM (%(subquery)s) "row")'
output_field = models.JSONField()
Create a Serializer Field to display only the needed information
class BJSONMediaField(serializers.Field):
def to_representation(self, value):
return {"type": value['resource_type'], "public_id": value['public_id'], "version": value['version']}
Create the serializer using this Custom field to map the annotated fields
class BJSONMediaSerializer(serializers.Serializer):
media_detail = BJSONMediaField(source="metadata")
url = serializers.CharField(source="metadata.url")
type = serializers.CharField()
height = serializers.IntegerField(source='metadata.height')
width = serializers.IntegerField(source='metadata.width')
in the get_queryset method of the viewset, create the annotations using the custom Subquery
def get_queryset(self):
category_media_source = PostMedia.objects.filter(categories=OuterRef('pk'))[:1]
post_media_source = UserMedia.objects.filter(posts__categories=OuterRef('pk'))[:1]
categories = Categories.objects.filter(is_public=True).annotate(
category_media_source=ToJSONB(category_media_source),
post_media_source=ToJSONB(post_media_source),
)
return categories