django - 在 django : the last post of each user 中过滤

Question

我有 2 个模型， Author 和 Post ，我如何制作一个过滤器，可以在一行中选择每个 Author 的最后一篇文章(通过 id 字段)？，对我来说不好的方法是:

authors = Author.objects.all()
queryset = []
for author in authors:
    posts = Post.objects.filter(author=author).order_by('-id')
    if loc:
        queryset.append(posts[0])

具体来说，这是为了过滤我的 Tastypie 资源“PostResource”，这个过滤器只能给我每个用户的最后一篇帖子，按创建排序

带有 okm answer 和 tastypie 自定义过滤器的完整解决方案:

class LocationResource(ModelResource):
    user = fields.ForeignKey(AccountResource,'user' )
    class Meta:
        queryset = Location.objects.all().order_by('-id')
        resource_name = 'location'
        #excludes = ['id',]
        list_allowed_methods = ['post','get']
        authentication = ApiKeyAuthentication()
        authorization= Authorization()
        filtering = {'user': ALL_WITH_RELATIONS}

    def obj_create(self, bundle, **kwargs):
        if bundle.request.method == 'POST':
            return super(LocationResource, self).obj_create(bundle, user=bundle.request.user)

    def apply_authorization_limits(self, request, object_list):
        return object_list.filter(user=request.user)

    def dehydrate(self, bundle):
        return bundle

    def build_filters(self, filters=None):
        if filters is None: #if you don't pass any filters at all
            filters = {}

        orm_filters = super(LocationResource, self).build_filters(filters)

        if('only_lasts' in filters):

            query = filters['only_lasts']

            sqs = Location.objects.values('user_id').annotate(max_id=models.Max('id')).values('max_id')

            orm_filters["pk__in"] = sqs

        return orm_filters

Answer 1

阅读the blog post关于在 SQL 中按组获取第一行。

如帖子中所述，您可以使用 IN 或 JOIN。

以IN为例:

SELECT * FROM post_table 
WHERE id IN (SELECT MAX(id) AS max_id FROM post_table GROUP BY author_id);

SQL 可以用 QuerySet 写成:

Post.objects.filter(pk__in=
    Post.objects.order_by().values('author_id').annotate(
        max_id=models.Max('id')).values('max_id'))

SELECT MAX(id) AS max_id FROM post_table GROUP BY author_id 的QuerySet 模式是:

Model.objects.order_by().values(...).annotate(foo=...).values('foo')
^------------^----------^-----------^-----------------^------------^
    \            \           \            \                \
base queryset     \        GROUP BY        \           SELECT column
or manager      remove possible           annotation part
                useless ordering          Min/Max/...

此外，您可以直接将 SQL 包装在 queryset.raw() 中。如果选择JOIN版本，.raw()方式更清晰。

请注意，IN 子句在您的数据库后端可能存在性能问题，如果性能至关重要，您需要分析和调整索引。