Django somehow cannot determine simple annotate function

Question

I'm trying to create an annotation on a queryset class that simply adds a boolean that is the result of some standard queries.

CustomQueryset(models.QuerySet):
    """ An extension of the traditional queryset to support
        filtering on accepting_offers """

    def annotate_with_accepting_offers(self):
        """ Add a lovely little variable to the SELECT that
            says if the listing is accepting offers.

            A <thing> is accepting offers when its:
                + not cancelled
                + expire date is today or in the future
                + has spaces left
        """
        return self.annotate(accepting_offers=Q(cancelled=False) & Q(expire_date__gte=date.today()) & Q(spaces_left__gt=0))

    def accepting_offers(self):
        """ Annotate with 'accepting_offers' and filter the results that are True """
        return self.annotate_with_accepting_offers().filter(accepting_offers=True)

    def not_accepting_offers(self):
        """ Annotate with 'accepting_offers' and filter the results that are False """
        return self.annotate_with_accepting_offers().filter(accepting_offers=False)

This unfortunately does not work, any ideas what annotation would?

If this was SQL, the top line would look like:

SELECT *, (cancelled=False AND expire_date >= ? AND spaces_left > 0) AS accepting_offers

Edit: The reasons I intend on making this annotation is to make filtering on the variable easier, which you can see in the next two proceeding functions.

These two methods would be used within a larger chain of queries, so (ironically) keeping it simple with an annotation should help.

Seeing the & bitwise operator raises some eyebrows, and is a logical operator in Python — Jared Goguen
– Jared Goguen, Commented Jan 28, 2018 at 16:44
Its the way recommended in the docs to add these objects, check it out. But you may be onto something — Stefan Collier
– Stefan Collier, Commented Jan 28, 2018 at 16:51
I'm not sure what you are expecting to happen by using Q objects inside an annotate clause; that isn't what they are for. What results are you hoping to get? — Daniel Roseman
– Daniel Roseman, Commented Jan 28, 2018 at 16:52
I've appended the reason why I want this to the Q. But my intention is to end up having a filter-able field on each of my <Thing>s. — Stefan Collier
– Stefan Collier, Commented Jan 28, 2018 at 16:57

Daniel Roseman · Accepted Answer · 2018-01-28 23:33:56Z

4

As I mentioned in the comments, this isn't what Q expressions are for at all. I think what you want is a conditional expression:

return self.annotate(
  accepting_offers=Case(
    When(cancelled=False, expire_date__gte=date.today(), spaces_left__gt=0, then=Value(True)),
    default_value=Value(False),
    output_field=models.BooleanField()
  )
)

edited Jan 28, 2018 at 23:33

answered Jan 28, 2018 at 17:02

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stefan Collier Over a year ago

That's brilliant, I'll give it a shot now

Stefan Collier Over a year ago

Ah no, I get the following error: FieldError: Cannot resolve expression type, unknown output_field. Any ideas?

Stefan Collier Over a year ago

Aha! Figured it out, we need to say the type it will output to. (Updating answer)

Stefan Collier Over a year ago

would you please update your answer to include the Case argument: output_field=models.BooleanField() and then I will accept your answer. (My edit was rejected)

Stefan Collier Over a year ago

Thanks for the edit, I'd be knackered in a months time when I have the same problem without it.

Collectives™ on Stack Overflow

Django somehow cannot determine simple annotate function

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related