Django自定义istruthy/isfalsy查询无法作用于ForeignKey的问题

问题背景 #

我在Django 4.2项目中需要一个能作用于所有数据库字段类型的通用istruthy/isfalsy查询。

模型示例（models.py）：

class MyModel(models.Model):
    my_charfield = models.CharField(...)
    my_decimalfield = models.DecimalField(...)
    my_datefield = models.DateField(...)
    my_boolfield = models.BooleanField(...)
    my_fkfield = models.ForeignKey(...)

期望执行的查询：

MyModel.objects.filter(my_charfield__isfalsy=True)
MyModel.objects.filter(my_decimalfield__isfalsy=True)
MyModel.objects.filter(my_datefield__isfalsy=True)
MyModel.objects.filter(my_boolfield__isfalsy=True)
MyModel.objects.filter(my_fkfield__isfalsy=True)

现有实现 #

我定义了两个自定义Lookup来实现该功能：

from django.db.models import Lookup, BooleanField, ForeignKey, Field
from django.db.models.fields import IntegerField, DecimalField, FloatField


class IsTruthy(Lookup):
    lookup_name = 'istruthy'

    def as_sql(self, compiler, connection):
        lhs, params = self.process_lhs(compiler, connection)

        if isinstance(self.lhs.output_field, BooleanField):
            return f"{lhs} = TRUE", params

        if isinstance(self.lhs.output_field, IntegerField):
            return f"{lhs} IS NOT NULL", params
        
        if isinstance(self.lhs.output_field, DecimalField):
            return f"{lhs} IS NOT NULL", params
        
        if isinstance(self.lhs.output_field, FloatField):
            return f"{lhs} IS NOT NULL", params

        if isinstance(self.lhs.output_field, ForeignKey):
            lhs = f"{lhs}_id"
            return f"{lhs} IS NOT NULL", params

        return f"{lhs} IS NOT NULL AND {lhs} <> ''", params


class IsFalsy(Lookup):
    lookup_name = 'isfalsy'

    def as_sql(self, compiler, connection):
        lhs, params = self.process_lhs(compiler, connection)

        if isinstance(self.lhs.output_field, BooleanField):
            return f"{lhs} = FALSE", params

        if isinstance(self.lhs.output_field, IntegerField):
            return f"{lhs} IS NULL", params

        if isinstance(self.lhs.output_field, DecimalField):
            return f"{lhs} IS NULL", params

        if isinstance(self.lhs.output_field, FloatField):
            return f"{lhs} IS NULL", params

        if isinstance(self.lhs.output_field, ForeignKey):
            lhs = f"{lhs}_id"
            return f"{lhs} IS NULL", params

        return f"{lhs} IS NULL OR {lhs} = ''", params


Field.register_lookup(IsTruthy)
Field.register_lookup(IsFalsy)

遇到的问题 #

这些自定义查询对所有字段都有效，除了ForeignKey。作用于ForeignKey时触发错误：

django.core.exceptions.FieldError: Unsupported lookup 'isfalsy' for ForeignKey or join on the field not permitted

同时调试困难：pdb.set_trace()、print语句、日志在as_sql()中都无法正常工作。

（注：我知道自定义查询中isfalsy=True和isfalsy=False的结果一致，这在我的项目场景中是可接受的）

完整报错栈：

Traceback (most recent call last):
  File "/home/luci/MyVENV/lib/python3.11/site-packages/IPython/core/interactiveshell.py", line 3577, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-3-efef204fd6a5>", line 1, in <module>
    MyModel.objects.filter(my_fkfield__isfalsy=True)
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/query.py", line 1436, in filter
    return self._filter_or_exclude(False, args, kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/query.py", line 1454, in _filter_or_exclude
    clone._filter_or_exclude_inplace(negate, args, kwargs)
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/query.py", line 1461, in _filter_or_exclude_inplace
    self._query.add_q(Q(*args, **kwargs))
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1545, in add_q
    clause, _ = self._add_q(q_object, self.used_aliases)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1576, in _add_q
    child_clause, needed_inner = self.build_filter(
                                 ^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1491, in build_filter
    condition = self.build_lookup(lookups, col, value)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1312, in build_lookup
    lhs = self.try_transform(lhs, lookup_name)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/luci/MyVENV/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1356, in try_transform
    raise FieldError(
django.core.exceptions.FieldError: Unsupported lookup 'isfalsy' for ForeignKey or join on the field not permitted.

解决方案 #

问题出在Django对ForeignKey字段的处理逻辑：当你对ForeignKey使用自定义lookup时，Django会尝试将其视为关联查询（即尝试join到关联表），而不是直接操作外键的ID字段。要解决这个问题，需要：

1. 调整Lookup实现并重新注册 #

修改Lookup类的逻辑，直接操作ForeignKey对应的ID字段，同时单独为ForeignKey注册lookup：

from django.db.models import Lookup, BooleanField, ForeignKey, Field
from django.db.models.fields import IntegerField, DecimalField, FloatField


class IsTruthy(Lookup):
    lookup_name = 'istruthy'

    def as_sql(self, compiler, connection):
        # 优先处理ForeignKey，直接获取对应的ID字段
        if isinstance(self.lhs.output_field, ForeignKey):
            # 获取外键字段的表别名和列名
            lhs_table = self.lhs.alias
            lhs_col = self.lhs.output_field.column
            lhs = f"{lhs_table}.{lhs_col}"
            return f"{lhs} IS NOT NULL", []
        
        # 其他字段原有逻辑
        lhs, params = self.process_lhs(compiler, connection)
        if isinstance(self.lhs.output_field, BooleanField):
            return f"{lhs} = TRUE", params
        elif isinstance(self.lhs.output_field, (IntegerField, DecimalField, FloatField)):
            return f"{lhs} IS NOT NULL", params
        else:
            return f"{lhs} IS NOT NULL AND {lhs} <> ''", params


class IsFalsy(Lookup):
    lookup_name = 'isfalsy'

    def as_sql(self, compiler, connection):
        # 优先处理ForeignKey，直接获取对应的ID字段
        if isinstance(self.lhs.output_field, ForeignKey):
            lhs_table = self.lhs.alias
            lhs_col = self.lhs.output_field.column
            lhs = f"{lhs_table}.{lhs_col}"
            return f"{lhs} IS NULL", []
        
        # 其他字段原有逻辑
        lhs, params = self.process_lhs(compiler, connection)
        if isinstance(self.lhs.output_field, BooleanField):
            return f"{lhs} = FALSE", params
        elif isinstance(self.lhs.output_field, (IntegerField, DecimalField, FloatField)):
            return f"{lhs} IS NULL", params
        else:
            return f"{lhs} IS NULL OR {lhs} = ''", params


# 同时注册到Field基类和ForeignKey，确保覆盖ForeignKey的处理逻辑
Field.register_lookup(IsTruthy)
Field.register_lookup(IsFalsy)
ForeignKey.register_lookup(IsTruthy)
ForeignKey.register_lookup(IsFalsy)

2. 调试方法 #

as_sql()中的调试代码不生效，是因为Django在构建查询时会先校验lookup的有效性，不会立即执行as_sql。可以在Lookup类中添加get_prep_lookup方法，在校验阶段触发调试：

class IsFalsy(Lookup):
    lookup_name = 'isfalsy'

    def get_prep_lookup(self):
        # 这里的调试代码会在lookup校验阶段执行
        print(f"当前处理字段类型：{type(self.lhs.output_field)}")
        print(f"字段输出类型：{self.lhs.output_field}")
        import pdb; pdb.set_trace()
        return super().get_prep_lookup()
    
    # 原有as_sql方法...

说明 #

修改后的lookup会直接操作ForeignKey对应的ID字段，避免Django触发关联查询，从而解决Unsupported lookup的错误。调试方法则能帮助你在lookup校验阶段查看字段信息，便于排查问题。

内容的提问来源于stack exchange，提问作者Luci