Django: filter a RawQuerySet

Question

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.).

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

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))

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(...)

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

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

Answer 1

I implemented Django raw queryset which supports filter(), order_by(), values() and values_list(). It will not work for any RAW query but for typical SELECT with some INNER JOIN or a LEFT JOIN it should work.

The FilteredRawQuerySet is implemented as a combination of Django model QuerySet and RawQuerySet, where the base (left part) of the SQL query is generated via RawQuerySet, while WHERE and ORDER BY directives are generared by QuerySet:

https://github.com/Dmitri-Sintsov/django-jinja-knockout/blob/master/django_jinja_knockout/query.py

It works with Django 1.8 .. 1.11.

It also has a ListQuerySet implementation for Prefetch object result lists of model instances as well, so these can be processed the same way as ordinary querysets.

Here is the example of usage:

https://github.com/Dmitri-Sintsov/djk-sample/search?l=Python&q=filteredrawqueryset&type=&utf8=%E2%9C%93

Answer 2

.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.

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.

Answer 3

There's another option: turn the RawQuerySet into a list, then you can do your sorting like this...

results_list.sort(key=lambda item:item.some_numeric_field, reverse=True)

and your filtering like this...

filtered_results = [i for i in results_list if i.some_field == 'something'])

...all programatically. I've been doing this a ton to minimize db requests. Works great!