Django：过滤一个RawQuerySet [英] Django: filter a RawQuerySet

问题描述

我有一些奇怪的查询，所以我有执行原始SQL。事实是，这个查询越来越大，并有很多可选的过滤器（排序，列标准等）。

i've got some weird query, so i have to execute raw SQL. The thing is that this query is getting bigger and bigger and with lots of optional filters (ordering, column criteria, etc.).

所以，给定这个查询： / p>

So, given the this query:

SELECT DISTINCT Camera.* FROM Camera c
     INNER JOIN cameras_features fc1 ON c.id = fc1.camera_id AND fc1.feature_id = 1
     INNER JOIN cameras_features fc2 ON c.id = fc2.camera_id AND fc2.feature_id = 2

这大概是Python代码：

This is roughly the Python code:

def get_cameras(features):
  query = "SELECT DISTINCT Camera.* FROM Camera c"
  i = 1
  for f in features:
    alias_name = "fc%s" % i
    query += "INNER JOIN cameras_features %s ON c.id = %s.camera_id AND %s.feature_id = " % (alias_name,alias_name,alias_name)
    query += " %s "
    i += 1
  return Camera.objects.raw(query, tuple(features))

这是非常好的，但我需要添加更多的过滤器订购，为例如，我需要按颜色过滤，按价格排序，它开始增长：

This is working great, but i need to add more filters and ordering, for example suppose i need to filter by color and order by price, it starts to grow:

#extra_filters is a list of tuples like:
# [('price', '=', '12'), ('color' = 'blue'), ('brand', 'like', 'lum%']
def get_cameras_big(features,extra_filters=None,order=None):
  query = "SELECT DISTINCT Camera.* FROM Camera c"
  i = 1
  for f in features:
    alias_name = "fc%s" % i
    query += "INNER JOIN cameras_features %s ON c.id = %s.camera_id AND %s.feature_id = " % (alias_name,alias_name,alias_name)
    query += " %s "
    i += 1
  if extra_filters:
    query += " WHERE "
    for ef in extra_filters:
      query += "%s %s %s" % ef #not very safe, refactoring needed
  if order:
    query += "order by %s" % order

  return Camera.objects.raw(query, tuple(features))

所以，我不喜欢它开始成长，我知道 Model.ob jects.raw（）返回一个RawQuerySet，所以我想做这样的事情：

So, i don't like how it started to grow, i know Model.objects.raw() returns a RawQuerySet, so i'd like to do something like this:

queryset = get_cameras( ... )
queryset.filter(...)
queryset.order_by(...)

但这不行。当然，我可以执行原始查询，之后获取一个具有数据的实际QuerySet，但是我将执行两个查询。喜欢：

But this doesn't work. Of course i could just perform the raw query and after that get the an actual QuerySet with the data, but i will perform two querys. Like:

raw_query_set = get_cameras( ... )
camera.objects.filter(id__in(raw_query_set.ids)) #don't know if it works, but you get the idea

我在想使用QuerySet init ，或者缓存可能会伎俩，但是还是无法做到这一点。

I'm thinking that something with the QuerySet init or the cache may do the trick, but haven't been able to do it.

推荐答案

.raw（）是一个终点。 Django无法对查询器执行任何操作，因为这将需要能够以某种方式将SQL解析为首先用于创建SQL的DBAPI。如果您使用 .raw（），则完全符合您构建所需的确切SQL。

.raw() is an end-point. Django can't do anything with the queryset because that would require being able to somehow parse your SQL back into the DBAPI it uses to create SQL in the first place. If you use .raw() it is entirely on you to construct the exact SQL you need.

如果您可以以某种方式将您的查询减少为可以由 .extra（）处理的内容。您可以使用Django的API构建您喜欢的任何查询，然后使用 .extra（）来附加SQL，但这将是您唯一的方法。

If you can somehow reduce your query into something that could be handled by .extra() instead. You could construct whatever query you like with Django's API and then tack on the additional SQL with .extra(), but that's going to be your only way around.

这篇关于Django：过滤一个RawQuerySet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！