Django: filter a RawQuerySet
i've got [some weird query][1], 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.

  [1]: http://stackoverflow.com/questions/9102907/can-you-solve-this-simple-sql-query

Django: filter a RawQuerySet
===

i've got [some weird query][1], 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.

  [1]: http://stackoverflow.com/questions/9102907/can-you-solve-this-simple-sql-query
