How to add a filter in a custom manager?

Question

I'm stuck with adding a filter to a custom manager in Django. This is my current custom manager that is working:

class VoteAwareManager(models.Manager):

    def _get_score_annotation(self):
        model_type = ContentType.objects.get_for_model(self.model)
        table_name = self.model._meta.db_table
        return self.extra(select={
            'active': 'select active from %s mh where mh.main_id = %s.id and mh.active = true and mh.date_begin = (select max(date_begin) from euvoudebicicletaengine_mainhistoric where main_id = mh.main_id) and mh.date_end >= now()' % (MainHistoric._meta.db_table, table_name),
            'row_num': '(row_number() over(order by (SELECT COALESCE(SUM(vote / ((extract(epoch from now() - time_stamp )/3600)+2)^1.5),0) FROM %s WHERE content_type_id=%d AND object_id=%s.id) DESC))' % (Vote._meta.db_table, int(model_type.id), table_name), # To know the position(#number) on the front page
            'score': 'SELECT COALESCE(SUM(vote / ((extract(epoch from now() - time_stamp )/3600)+2)^1.5),0) FROM %s WHERE content_type_id=%d AND object_id=%s.id' % (Vote._meta.db_table, int(model_type.id), table_name)
                }
        )

    def most_loved(self,):
        return self._get_score_annotation().order_by('-score')

    def most_hated(self):
        return self._get_score_annotation().order_by('score')

I need to add a filter to the most_loved and most_hated to active=True that will be the SQL equivalent to where active=true in the main sql expression.

Any clues on how to do it?

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

I think you probably need to write a SQL view (to replace your extra() function) and create a new unmanaged model for the view (including active as a field in your model).

As in this question. Or this (possibly out of date) one.

Then use the view in your _get_score_annotation and add a filter to the queryset you're getting from that function.

def _get_score_annotation(self):
    return ContentTypeView.objects.filter(# any filtering you need)

def most_loved(self,):
    return self._get_score_annotation().filter(active=True).order_by('-score')

edited May 23 '17 at 12:27

Community

1
1

answered Feb 20 '13 at 14:50

Aidan Ewen

13,049
8
63
88

Hi Aidan. Thanks for the reply. Adding the filter in that way gives me an error: "Cannot resolve keyword 'active' into field. Choices are: ...". It looks like the I cannot use the extra fields in the WHERE clause. Any clues on how to do it in another way? Best Regards, – André Feb 20 '13 at 14:55
OK, I see the issue (I thought that seemed a bit straight forward). – Aidan Ewen Feb 20 '13 at 15:01

How to add a filter in a custom manager?

1 Answers1