Django: RawQuerySet filter

Question

I have some kind of weird query , so I have to execute raw SQL. The fact is that this query is becoming more and more with many additional filters (ordering, column criteria, etc.).

So, given this request:

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 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 works fine, but I need to add more filters and arrange, for example, suppose I need to filter by color and by price 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 that Model.objects.raw() returns a RawQuerySet, so I would like to do something like this:

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

But that does not work. Of course, I could just execute the raw query and after that get the actual QuerySet with the data, but I will execute two queries. How:

 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 think something with a QuerySet init or cache might do the trick, but couldn't do it.

Answer 1

.raw() is the endpoint. Django cannot do anything with the query because it will require the ability to somehow parse your SQL back into the DBAPI, which it uses to create SQL in the first place. If you use .raw() , you need to completely construct the exact SQL.

If you can somehow reduce your request to what .extra() could be handled. You can build any query you like with the Django API, and then apply additional SQL with .extra() , but that will be your only way.

Answer 2

To build a set of queries, each method returns a new query, so you will need to store this new query every time you add it. Therefore changing the pseudocode ::

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

This will result in a more complex set of queries.

Answer 3

There is another option: include RawQuerySet in the list, then you can sort as follows:

 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 programmatically. I am doing this ton to minimize db requests. It works great!

Answer 4

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

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

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 lists of the resulting objects of the model instance object, so they can be processed in the same way as regular queries.

Here is a usage example:

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

Answer 5

Another thing you can do is that if you cannot convert it to a regular QuerySet, create a view in the database backend. It basically executes the request in the view when you access it. In Django, you will create an unmanaged model to join the view. With this model, you can apply a filter as if it were a regular model. With your foreign keys, you must set the argument on_delete arg.DO_NOTHING.

Additional information on unmanaged models: https://docs.djangoproject.com/en/2.0/ref/models/options/#managed