如何使用来自其他模型的信息来注释查询集，或者在Django Rest Framework中对使用raw构建的查询集进行分页？

I wanted to add a custom route to my ViewSet; essentially I want to pull a list of all instances of another model, and annotate the records with information from the model used for the viewset (in this case I want to pull a list of all tenses and annotate them with a boolean flag representing whether or not the currently logged in user has access to the given tense).

我想为我的ViewSet添加一个自定义路由;本质上我想拉出另一个模型的所有实例的列表,并用来自用于视图集的模型的信息注释记录(在这种情况下,我想拉出所有时态的列表并使用表示是否的布尔标记来注释它们)不是当前登录的用户可以访问给定时态)。

I was able to accomplish what I wanted using the raw query below, but then things got complicated when I went to use the builtin pagination.

我能够使用下面的原始查询完成我想要的东西,但是当我使用内置分页时,事情变得复杂了。

class UserViewSet(viewsets.ModelViewSet):
  permission_classes = (IsAuthenticated,)
  queryset = VerbUser.objects.all()
  serializer_class = VerbUserSerializer

  @list_route(methods=['patch', 'get'])
  def tenses(self, request):
    user_id = request.user.id
    #If this is a get request, pull all the tenses and annotate them with a boolean field indicating if
    #the currently logged in user has chosen this tense
    if request.method == 'GET':
      queryset = Tense.objects.raw('''
        SELECT T.id, T.tense, T.tense_translation, T.mood, T.mood_translation, (U.verbuser_id IS NOT NULL) AS selected
        FROM verbs_tense T
        LEFT OUTER JOIN users_verbuser_tenses U
        ON T.id = U.tense_id
        AND U.verbuser_id = %s
        ORDER BY T.id''', [user_id])

      serializer = UserTenseSerializer(queryset, many=True)
      return Response(serializer.data)

Is there any way to accomplish this? I have ended up doing something like this to leverage the builtin pagination:

有没有办法实现这个目标?我最终做了类似这样的事情来利用内置分页:

@list_route()
def infinitives(self, request):
  user_id = request.user.id
  #If this is a get request, pull all the infinitives and annotate them with a boolean field indicating if
  #the currently logged in user has chosen this infinitive
  if request.method == 'GET':
    queryset = Infinitive.objects.all()

    #We need to manually paginate this, as we\'re using a custom serializer
    page = self.paginate_queryset(queryset)

    #TODO consider pagination or caching this?
    serializer = UserInfinitiveSerializer(page, many=True, infinitives=[x.id for x in request.user.infinitives.all().only('id')])
    return self.get_paginated_response(serializer.data)

And then changing the serializer as follows:

然后按如下方式更改序列化程序:

class UserInfinitiveSerializer(serializers.ModelSerializer):
    selected = serializers.SerializerMethodField()

    class Meta:
        model = Infinitive
        fields = ('id', 'name', 'translation', 'top_100', 'selected')

    def get_selected(self, obj):
        return obj.id in self.infinitives

    def __init__(self, args, **kwargs):
        infinitives = kwargs.pop('infinitives', None)
        super(UserInfinitiveSerializer, self).__init__(args, **kwargs)
        self.infinitives = infinitives

While this works, I would prefer to just be able to annotate the original query, and not have to do multiple queries.

虽然这有效,但我更愿意只能注释原始查询,而不必进行多次查询。

1 个解决方案

#1

You can define an aggregate for the null check (assuming postgresql):

您可以为null检查定义聚合(假设postgresql):

from django.db.models import Aggregate
class AnyNotNull(Aggregate):
    function = 'ANY'
    template = 'true = %(function)s(array_agg(%(expressions)s is not null))'

And use it in a query:

并在查询中使用它:

Tense.objects.filter(
    Q(verbuser_tenses__isnull = True) |
    Q(verbuser_tenses__verbuser_id = user_id)
    ).annotate(selected = AnyNotNull('verbuser_tenses__verbuser_id')
    ).order_by('id')

This will annotate the tense objects with true in selected if the user has access.

如果用户具有访问权限,这将在选中时使用true来注释时态对象。

#1