How to execute bitwise database queries in Django?

Question

How to execute bitwise queries in a DB using Django? I did not find anything in the docs. Should I get a request and then filter programmatically?

If you're interested, I use bitwise operators as an alternative to IN () statements in very large and complex queries to improve performance.
I have a database containing millions of elements (records). Some fields use a binary representation of the item property. For example: the Color field can have several values, therefore it is structured like this:

0001 - Red
0010 - Green
0100 - Blue
1000 - White

(these are binary values)
Therefore, if an element has red and blue colors, the Color field will contain 0101.
When a user requests a DB, I use bitwise OR to search for matches (instead of IN () , which is very slow).

Answer 1

You can perform bitwise operations at the database level with F objects .

, , field & mask > 0 (field > 0) AND (field >= (field & mask)). , mask ((field & mask) == mask), , sql AND. , , . ( QuerySet . django, has_one_of has_all , PathThroughManager). . 1 * F - , django ( 1.5) sql (colors >= colors & mask, , TRUE & mask)

import operator
from django.db import models
from django.db.models import Q, F

_bit = lambda x: 2**(x-1)
RED = _bit(1)
GREEN = _bit(2)
BLUE = _bit(3)
WHITE = _bit(4)


class ItemColorsQuerySet(models.QuerySet):

    def has_one_of(self, colors):
        """
            Only those that has at least one of provided colors
        """
        return self.filter(
            colors__gt=0,
            # field value contains one of supplied color bits
            colors__gte=1 * F('colors').bitand(reduce(operator.or_, colors, 0))
        )

    def has_all(self, colors):
        """
            Has all provided colors (and probably others)
        """
        # filter conditions for all supplied colors: 
        # each one is "field value has bit that represents color"
        colors_q = map(lambda c: Q(colors__gte=1 * F('colors').bitand(c)), colors)
        # one complex Q object merged via sql AND:
        # colors>0 and all color-bit conditions
        filter_q = reduce(operator.and_, colors_q, Q(colors__gt=0))
        return self.filter(filter_q)


class Item(models.Model):

    name = models.CharField(max_length=100, unique=True)
    # can handle many colors using bitwise logic. Zero means no color is set.
    colors = models.PositiveIntegerField(default=0)

    objects = ItemColorsQuerySet.as_manager()

Answer 2

postgres db . extra() params django orm.

:

SomeModel.objects.extra(where=['brand_label & 3 = 3'])

Answer 3

django-bitfield, PostgreSQL (, MySQL)

Answer 4

as well as @Ivan Klass,

You can use bitandeither bitorfrom django orm F ,

# filter Red and Blue(0101)
Color.objects.annotate(
  color_filter=F('color').bitand(0101)
).filter(color_filter__gte=0101)

 # filter Red or Blue(0101)
Color.objects.annotate(
  color_filter=F('color').bitand(0101)
).filter(color_filter__gt=0)