如何在Django中执行数据库按位查询? [英] How to perform DB bitwise queries in Django?

问题描述

如何使用Django在数据库上执行按位查询?
我还没有在文档中找到任何有关它的信息.
我应该检索查询集，然后以编程方式进行过滤吗?

How can I perform bitwise queries on the DB with Django?
I haven't found anything about it in the docs.
Should I retrieve a queryset and then filter programmically?

如果您有兴趣，我可以在非常大而复杂的查询中使用按位运算来代替 IN()语句，以提高性能.
我有一个包含数百万个项目(记录)的数据库.一些字段使用item属性的二进制表示形式.
例如:颜色字段可以有多个值，因此其结构如下:

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

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

(这些是二进制值)
因此，如果某项具有红色和蓝色，则颜色字段将包含0101.
当用户查询数据库时，我使用按位或运算符查找匹配项(而不是非常慢的 IN()).

(these are binary values)
So if an item has Red and Blue colors, the Color field will contain 0101.
When a user queries the DB, I use bitwise-OR to find matches (instead of IN() which is very slow).

推荐答案

您可以使用如果字段为非负数，则意味着条件field & mask > 0可以重写为(field > 0) AND (field >= (field & mask)).如果要检查mask的所有位是否都适用((field & mask) == mask)，则可以为每个位构建先前的表达式，然后通过sql AND合并条件.请参阅示例如何完成. (自定义QuerySet只是为了方便.如果使用旧的django版本，则可以将has_one_of和has_all作为单独的函数或类方法来实现，或者更好的

If field is non-negative, it means that condition field & mask > 0 can be re-written as (field > 0) AND (field >= (field & mask)). If you want to check if all bits of mask apply ((field & mask) == mask), you can build previous expression for each bit and then merge conditions via sql AND. Please see example how it can be done. (Custom QuerySet is just for convenience. If you use old django versions you can implement has_one_of and has_all as separate functions or classmethods, or better PathThroughManager). Note 1 * F is a workaround to force parenthesis over bitwise operation, otherwise django (as for version 1.5) will produce bad sql (colors >= colors & mask, comparison has higher priority, so it will mean 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__lt=F('colors') + (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()

这篇关于如何在Django中执行数据库按位查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！