Logical OR on F-expressions in Django ORM

user_3609568 0
Viewed 685

I have a query like

User.objects.annotate(
    x=Value(False, output_field=BooleanField()),
    y=Value(True, output_field=BooleanField())
).annotate(
    z=F('x').bitor(F('y'))  # HOW TO DO THIS?
).values('z')

which works with SQLite but not with PostgreSQL. The error is 

LINE 1: SELECT (false | true) AS "z" FROM "auth_user"  LIMIT 21
                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

How should I implement the logical OR on annotated fields that works consistently?

Thank you.

postgresql orm sqlite django
2 Answers

Django doesn't provide logical operators for Expression objects on its own, but deeper into the framework both Value and F inherit from the Combinable class. This class actually provides us with operators we can use, unfortunately not boolean ones.

If both of our values are resolvable to an expression (Values are) we can pass the operator to the database ourselves. To do this we will do exactly what Combinable does - initialize the CombinedExpression object:

User.objects.annotate(
        x=Value(False, output_field=BooleanField()),
        y=Value(True, output_field=BooleanField())
    ).annotate(
        z=CombinedExpression(lhs=F('x'), rhs=F('y'), connector="OR")
    ).values('z')

This solution might not be compliant with all databases but it works with PostgreSQL.

user_66725440

Cast it to interger type when using bitwise operator

from django.db.models.functions import Cast

User.objects.annotate(
    x=Value(False, output_field=BooleanField()),
    y=Value(True, output_field=BooleanField())
).annotate(
    z=Cast(Cast(F('x'), IntergerField()).bitor(Cast(F('y'), IntergerField())), BooleanField())
).values('z')
user_84954830
Related