Для каждого пользователя я хочу определить какие лекции и курсы им доступны. Но при сериализации данных возникает N+1 зависимость.
Мои модели:
# accounts/models.py
class User(AbstractUser):
...
membership = models.ForeignKey(Membership, null=True, blank=True)
lectures = models.ManyToManyField(Lecture, blank=True, related_name='user_lectures')
courses_order = ArrayField(models.IntegerField(), null=True, blank=True)
....
def __str__(self):
return self.username
# membership/models.py
class Membership(models.Model):
...
courses = models.ManyToManyField(Course, related_name='membership_courses')
def __str__(self):
return self.name
# courses/models.py
class Course(models.Model):
...
pass
class Lecture(models.Model):
...
course = models.ForeignKey(Course, related_name='lecture_course')
Вьюха:
# courses/views.py
class CourseListView(APIView):
"""
A view that returns list of courses with lectures.
"""
permission_classes = (IsAuthenticated,)
def get(self, request):
try:
membership = Membership.objects.get(user=request.user)
courses = membership.courses.all()
user = request.user
if request.user.courses_order:
pk_list = request.user.courses_order
clauses = ' '.join(['WHEN course_id=%s THEN %s' % (pk, i) for i, pk in enumerate(pk_list)])
ordering = 'CASE %s END' % clauses
queryset = courses.filter(pk__in=pk_list).extra(select={'ordering': ordering}, order_by=('ordering',))
else:
queryset = courses.order_by("sorting_number")
if queryset.filter(is_free=True).exists:
course = queryset.filter(is_free=True)
lectures = Lecture.objects.filter(course__in=course)
user.lectures.add(*lectures)
if Lecture.objects.filter(course__in=queryset, is_locked=False).exists():
lectures = Lecture.objects.filter(course__in=queryset, is_locked=False)
user.lectures.add(*lectures)
if request.user.membership.type == 'vip':
lectures = Lecture.objects.filter(course__in=queryset)
if len(user.lectures.all()) != len(lectures):
user.lectures.add(*lectures)
serializer = CourseSerializer(queryset, many=True, context={'user': request.user})
return Response({'courses': serializer.data}, status=status.HTTP_200_OK)
except Membership.DoesNotExist:
raise Http404
И собственно сериалайзер:
class LectureSerializer(serializers.ModelSerializer):
locked = serializers.SerializerMethodField('_get_lecture_status')
def _get_lecture_status(self, obj):
user = self.context.get('user')
if obj in user.lectures.all():
return False
else:
return True
class Meta:
model = Lecture
fields = ('id', 'sorting_number', 'name', 'locked', 'video_preview', 'video_id', 'description',
'external_files')
class CourseSerializer(serializers.ModelSerializer):
lectures = serializers.SerializerMethodField('_get_lectures')
total_lectures_number = serializers.SerializerMethodField('_get_total_lectures_number')
status = serializers.SerializerMethodField('_get_lecture_status')
opened_lectures_number = serializers.SerializerMethodField('_get_opened_lectures_number')
def _get_lectures(self, course):
lectures = Lecture.objects.filter(course_id=course.id, is_published=True).order_by('sorting_number')
return LectureSerializer(lectures, many=True, context={'user': self.context.get('user')}).data
def _get_total_lectures_number(self, course):
lectures_number = Lecture.objects.filter(course_id=course.id, is_published=True).count()
return lectures_number
def _get_lecture_status(self, course):
user = self.context.get('user')
total_lectures_count = Lecture.objects.filter(course_id=course.id, is_published=True).count()
user_opened_lectures_count = user.lectures.filter(course=course).count()
# 0 - all
# 1 - not all
# 2 - empty
if user_opened_lectures_count == 0:
return 2
elif user_opened_lectures_count == total_lectures_count and user_opened_lectures_count != 0:
return 0
else:
return 1
def _get_opened_lectures_number(self, course):
user = self.context.get('user')
return user.lectures.filter(course=course).count()
class Meta:
model = Course
fields = ('id', 'sorting_number', 'name', 'description', 'opened_lectures_number', 'total_lectures_number',
'status', 'lectures')
На локальной машине при незначительном количестве лекций и курсов в базу летит 64 запроса, 57 из которых дублируются.
На тестовом сервере при чуть большем количестве данных в базу летит 110 запросов, что занимает по времени приблизительно 15 секунд.
Тестил через Django Debug Toolbar
Существует ли возможность избежать это и как?
