Annotating a query with comparison result e.g. F('foo') == F('bar')

[Daniel Hepper]

I'm looking for a way to build a query that resembles this:

    select id, user_id, user_id=7 as mine from app_status order by mine desc;

(This question originally comes from a thread on Reddit).

My first instinct was that it should be possible with an F expression:

     Status.objects.annotate(mine=F('user_id') == 7).order_by('-mine'):

This doesn't work. Looking at the Django 1.11 source [2] reveals that F.__eq__ isn't overwritten. It is overwritten in Master [3], but returns a boolean instead of an expression.

I tried monkey-patching F.__eq__ like this:

>>> F.__eq__ = lambda self, other: self._combine(other, '=', False)

This produces the expected result:

>>> for s in Status.objects.annotate(mine=F('user_id')==7).values('id', 'user_id', 'mine').order_by('-mine'): print(s)

...

{'id': 6, 'user_id': 7, 'mine': 1}

{'id': 7, 'user_id': 7, 'mine': 1}

{'id': 8, 'user_id': 7, 'mine': 1}

{'id': 1, 'user_id': 6, 'mine': 0}

{'id': 2, 'user_id': 6, 'mine': 0}

{'id': 3, 'user_id': 6, 'mine': 0}

{'id': 4, 'user_id': 6, 'mine': 0}

{'id': 5, 'user_id': 6, 'mine': 0}

{'id': 9, 'user_id': 8, 'mine': 0}

{'id': 18, 'user_id': 10, 'mine': 0}

{'id': 19, 'user_id': 10, 'mine': 0}

{'id': 20, 'user_id': 10, 'mine': 0}

{'id': 21, 'user_id': 10, 'mine': 0}

{'id': 22, 'user_id': 10, 'mine': 0}

{'id': 23, 'user_id': 10, 'mine': 0}

{'id': 24, 'user_id': 10, 'mine': 0}

{'id': 25, 'user_id': 10, 'mine': 0}

{'id': 26, 'user_id': 10, 'mine': 0}

{'id': 27, 'user_id': 10, 'mine': 0}

{'id': 28, 'user_id': 11, 'mine': 0}

{'id': 29, 'user_id': 12, 'mine': 0}

{'id': 30, 'user_id': 12, 'mine': 0}

{'id': 31, 'user_id': 12, 'mine': 0}

{'id': 32, 'user_id': 12, 'mine': 0}

So my questions are:

- is it currently possible to annotate a query with a comparison without resorting to raw SQL?

- why do F-expressions not support comparisons?

Cheers,

Daniel

[1] https://www.reddit.com/r/django/comments/6x9j5h/custom_order_by_parameter/

[2] https://github.com/django/django/blob/1.11/django/db/models/expressions.py

[3] https://github.com/django/django/blob/master/django/db/models/expressions.py#L501

[Matthew Pava]

I would approach your problem differently, though I don’t see why Django shouldn’t support such a construct in the future.

I would use a Case…When construct.

 

Status.objects.annotate(mine=Case(When(user_id=7,then=True), default=False, output_field=BooleanField()).order_by(‘-mine’)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8992f41d-74cf-4edb-acf1-8f55715c7a79%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.