Django 1.9: Create complex custom filter method for QuerySet

Question

The goal is to create a custom_filter method that can be chained to standard Django filter methods. The custom_filter method likely requires some raw SQL code. In the best case, the QuerySet would still be evaluated lazy.

In the end, such a command would be great:

apple_query_set = Apple.objects.filter(<any standard filtering>).custom_filter()

This is the model:

class Apple(models.model):
    a = models.IntegerField()
    b = models.IntegerField()
    date = models.DateField()

The goal of the custom_filter is to group the Apple instances by (a,b), and for each group return only the newest instance according to date.

The raw SQL code for such a filter is as follows:

custom_filter_raw_sql = """
SELECT t1.id
FROM app_apple AS t1
INNER JOIN (SELECT a, b, max(date) AS max_date
            FROM app_apple
            GROUP BY a, b) AS t2
ON t1.a = t2.a AND t1.b = t2.b AND t1.date = t2.max_date;
"""

So far, in order to add the custom_filter functionality, I've tried (unsuccessfully) adding objects = AppleQuerySet.as_manager() to the Apple class, with:

 class AppleQuerySet(models.QuerySet):
    def custom_filter(self):
        subquery = """
        SELECT t1.id
        FROM app_apple AS t1
        INNER JOIN (SELECT a, b, max(date) AS max_date
                    FROM app_apple
                    GROUP BY a, b) AS t2
        """
        condition = "t1.a = t2.a AND t1.b = t2.b AND t1.date = t2.max_date"
        return self.extra(tables=[subquery], where=[condition])

However, I'm not sure this approach stands a chance to work, as the custom query should not only work on all Apple instances (Apple.objects.), but it should be possible to chain it to a filtered queryset (Apple.objects.filter())

What is the best approach to create this custom chainable, (lazy) custom_filter functionality? Where am I going wrong? Thanks a lot!

Answer 1

This is an alternative way, but I wonder if you could only use a combination of order_by and distinct to achieve the desired results:

Apple.objects.order_by('a', 'b', '-date').distinct('a', 'b')

This combination works if you keep the ordering of fields same in order_by and distinct.

And, this way you can also user the chained filter beforehand if you need to.

Some explanation:

Using following will only put all the objects with similar a and b together

Apple.objects.order_by('a', 'b')

But, you can order the objects in a group (with same a and b values) further by -date (in decreasing order)

Apple.objects.order_by('a', 'b', '-date')

Now, all the objects which have similar a and b are together and in each group first element has the latest date. So, we can keep the ones at the top by using distinct('a', 'b')

Apple.objects.order_by('a', 'b', '-date').distinct('a', 'b')