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

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')

 

From: django-users@googlegroups.com [mailto:django-users@googlegroups.com] On Behalf Of Daniel Hepper
Sent: Friday, September 1, 2017 2:32 AM
To: Django users
Subject: Annotating a query with comparison result e.g. F('foo') == F('bar')

 

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

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@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.