在Django REST ListAPI视图中分页原始SQL查询的最佳方法？ [英] Best way to paginate a raw SQL query in a Django REST ListAPI view?

问题描述

我有一个原始的SQL查询，用于为Django REST ListAPI视图构建查询集。它符合以下条件（请原谅无意义的名字）：

I have a raw SQL query that I use to build a query set for a Django REST ListAPI view. It is along the lines of the following (please excuse the meaningless names):

class MyView(ListAPIView):
    serializer_class = MySerializer
    paginate_by = 10
    def get_queryset(self):
        params = {
            "uid": str(self.request.user.id),
            "param": str(self.kwargs['param'])
        }
        query = 'SELECT f.id ' \
            'FROM myapp_foo f, myapp_bar b ' \
            'WHERE b.foo_id = f.id AND ' \
            'b.param >= %(param)s AND ' \
            'f.dt_tm >= NOW() AND ' \
            '(SELECT COUNT(*) FROM myapp_baz z ' \
            'WHERE z.user_id = %(uid)s AND ' \
            'z.qux_id = f.qux_id) = 0 ' \
            'ORDER BY f.dt_tm;'
        return Foo.objects.raw(query, params)

这给出错误：

object of type 'RawQuerySet' has no len()

我想使用类似的SQL查询计算计数，然后使用LIMIT和OFFSET参数进行分页。我已经阅读了一些建议，其中列表项被计数以获得len，但是这似乎不令人满意，因为除非查询中有一个极小的限制（在任何情况下都会击败分页的目的），否则这将是无效的。

I would like to calculate a count with a similar SQL query and then use the LIMIT and OFFSET parameters to do the pagination. I have read some suggestions where the list items are counted to get the len but this does not seem satisfactory, as it would be inefficient unless there was a small LIMIT in the query (which in any case would defeat the purpose of having pagination).

更新：
我刚刚注意到paginate_by正在等待废止。

Update: I just noticed paginate_by is pending deprecation.

开始如何向返回的对象添加计数方法？

To start with how would I add a count method to the returned object?

推荐答案

如果将原始查询器转换为列表在返回之前，应该阻止'RawQuerySet'没有len（）错误。

If you cast the raw queryset to a list before returning it, that should prevent the 'RawQuerySet' has no len() error.

return list(Foo.objects.raw(query))

正如你所说，这将无效，因为它将加载整个查询。

As you say, that will be inefficient, as it will load the entire queryset.

可能编写一个自定义分页类，它使用limit和offset进行有效的分页，并使用它在您的看法中，使用 pagination_class 属性。

It might be possible to write a custom pagination class, which paginates efficiently using limit and offset, and use it in your view with the pagination_class attribute.

这篇关于在Django REST ListAPI视图中分页原始SQL查询的最佳方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！