在Django过滤器中使用关系时过滤器的OR定义
我有三种具有简单关系的模型，如下所示:
I have three models with a simple relation as below:
class Person(models.Model):
first_name = models.CharField(max_length=20)
last_name = models.CharField(max_length=20)
class PersonSession(models.Model):
start_time = models.DateTimeField(auto_now_add=True)
end_time = models.DateTimeField(null=True,
blank=True)
person = models.ForeignKey(Person, related_name='sessions')
class Billing(models.Model):
DEBT = 'DE'
BALANCED = 'BA'
CREDIT = 'CR'
session = models.OneToOneField(PersonSession,
blank=False,
null=False,
related_name='billing')
STATUS = ((BALANCED, 'Balanced'),
(DEBT, 'Debt'),
(CREDIT, 'Credit'))
status = models.CharField(max_length=2,
choices=STATUS,
blank=False,
default=BALANCED
)
views.py
class PersonFilter(django_filters.FilterSet):
start_time = django_filters.DateFromToRangeFilter(name='sessions__start_time',
distinct=True)
billing_status = django_filters.ChoiceFilter(name='sessions__billing__status',
choices=Billing.STATUS,
distinct=True)
class Meta:
model = Person
fields = ('first_name', 'last_name')
class PersonList(generics.ListCreateAPIView):
queryset = Person.objects.all()
serializer_class = PersonSerializer
filter_backends = (django_filters.rest_framework.DjangoFilterBackend)
filter_class = PersonFilter
我想从个人端点获取帐单，这些帐单在计费中的状态为DE并且在一段时间内:
I want to get billings from person endpoint which have DE status in billing and are between a period of time:
api/persons?start_time_0=2018-03-20&start_time_1=2018-03-23&billing_status=DE
但是结果不是我想要的，这将返回所有人在该时间段内都有一个会话并且具有DE状态的账单，无论该账单是否在该周期内.
But the result is not what I were looking for, this returns all persons has a session in that period and has a billing with the DE status, whether that billing is on the period or not.
换句话说，似乎在两个过滤器字段之间使用or操作，我认为这篇文章与此问题有关，但是当前我找不到找到所需结果的方法.我正在使用djang 1.10.3.
In other words, it seems use or operation between two filter fields, I think this post is related to this issue but currently I could not find a way to get the result I want. I am using djang 1.10.3.
我尝试编写一个示例，以显示我需要的东西以及从Django得到的东西筛选.如果在示例中使用下面的查询让人，那么我只有两个人:
I try to write an example to show what I need and what I get from django filter. If I get persons using below query in the example, I got just two person:
select *
from
test_filter_person join test_filter_personsession on test_filter_person.id=test_filter_personsession.person_id join test_filter_billing on test_filter_personsession.id=test_filter_billing.session_id
where
start_time > '2000-02-01' and start_time < '2000-03-01' and status='DE';
哪一个使我只有人1和2.但是，如果我从url获得预期的相似内容，我就会得到所有人，相似的url(至少一个我希望相同的URL)如下:
Which gets me just person 1 and 2. But if I get somethings expected similar from url I would get all of persons, the similar url (at least one which I expected to be the same) is as below:
http://address/persons?start_time_0=2000-02-01&start_time_1=2000-03-01&billing_status=DE
Edit2
这是我在示例中查询所依据的数据，使用它们，您可以看到在我上面提到的查询中必须返回的内容:
Edit2
This is the data that my queries in the example are upon and using them you can see what must returns in queries that I mentioned above:
id | first_name | last_name | id | start_time | end_time | person_id | id | status | session_id
----+------------+-----------+----+---------------------------+---------------------------+-----------+----+--------+------------
0 | person | 0 | 0 | 2000-01-01 16:32:00+03:30 | 2000-01-01 17:32:00+03:30 | 0 | 0 | DE | 0
0 | person | 0 | 1 | 2000-02-01 16:32:00+03:30 | 2000-02-01 17:32:00+03:30 | 0 | 1 | BA | 1
0 | person | 0 | 2 | 2000-03-01 16:32:00+03:30 | 2000-03-01 17:32:00+03:30 | 0 | 2 | DE | 2
1 | person | 1 | 3 | 2000-01-01 16:32:00+03:30 | 2000-01-01 17:32:00+03:30 | 1 | 3 | BA | 3
1 | person | 1 | 4 | 2000-02-01 16:32:00+03:30 | 2000-02-01 17:32:00+03:30 | 1 | 4 | DE | 4
1 | person | 1 | 5 | 2000-03-01 16:32:00+03:30 | 2000-03-01 17:32:00+03:30 | 1 | 5 | DE | 5
2 | person | 2 | 6 | 2000-01-01 16:32:00+03:30 | 2000-01-01 17:32:00+03:30 | 2 | 6 | DE | 6
2 | person | 2 | 7 | 2000-02-01 16:32:00+03:30 | 2000-02-01 17:32:00+03:30 | 2 | 7 | DE | 7
2 | person | 2 | 8 | 2000-03-01 16:32:00+03:30 | 2000-03-01 17:32:00+03:30 | 2 | 8 | BA | 8
Edit3
我尝试使用prefetch_related联接表并按预期方式获得结果，因为我认为额外的联接会导致此问题，但这无法正常工作，但我仍然得到相同的结果，并且没有任何效果.
Edit3
I try using prefetch_related to join tables and get results as I expected because I thought that extra join causes this problem but this did not work and I still get the same result and this had not any effects.
此问题具有相同的问题.
我还没有解决方案.但是我认为对问题的简要总结将比我在工作中提出更多更好的想法！
据我了解；您的核心问题是两个前提条件的结果:
I don't have a solution yet; but I thought a concise summary of the problem will set more and better minds than mine at work!
From what I understand; your core issue is a result of two pre-conditions:
- 在相关模型上定义了两个离散滤波器的事实；导致过滤器跨越多值关系
-
FilterSet实现过滤的方式
- The fact that you have two discrete filters defined on a related model; resulting in filter spanning-multi-valued-relationships
- The way
FilterSetimplements filtering
让我们更详细地了解这些内容:
过滤器跨多值关系
这是一个很好的资源，可以更好地理解问题先决条件#1: https://docs.djangoproject.com/en/2.0/topics/db/queries/#spanning-multi-valued-relationships
Let us look at these in more detail:
filter spanning-multi-valued-relationships
This is a great resource to understand issue pre-condition #1 better: https://docs.djangoproject.com/en/2.0/topics/db/queries/#spanning-multi-valued-relationships
本质上，start_time过滤器将.filter(sessions__start_time=value)添加到查询集，而billing_status过滤器将.filter(sessions_billing_status=value)添加到过滤器.这会导致上述跨多值关系"问题，这意味着它将在这些过滤器之间执行OR而不是您需要的AND.
Essentially, the start_time filter adds a .filter(sessions__start_time=value) to your Queryset, and the billing_status filter adds a .filter(sessions_billing_status=value) to the filter. This results in the "spanning-multi-valued-relationships" issue described above, meaning it will do an OR between these filters instead of an AND as you require it to.
这让我开始思考，为什么我们在start_time过滤器中看不到相同的问题?但是这里的窍门是它被定义为DateFromToRangeFilter；它在内部使用带有__range=构造的单个过滤器查询.相反，如果它执行了sessions__start_time__gt=和sessions__start_time__lt=，我们这里将遇到相同的问题.
This got me thinking, why don't we see the same issue in the start_time filter; but the trick here is that it is defined as a DateFromToRangeFilter; it internally uses a single filter query with the __range= construct. If instead it did sessions__start_time__gt= and sessions__start_time__lt=, we would have the same issue here.
对话很便宜；给我看代码
Talk is cheap; show me the code
@property
def qs(self):
if not hasattr(self, '_qs'):
if not self.is_bound:
self._qs = self.queryset.all()
return self._qs
if not self.form.is_valid():
if self.strict == STRICTNESS.RAISE_VALIDATION_ERROR:
raise forms.ValidationError(self.form.errors)
elif self.strict == STRICTNESS.RETURN_NO_RESULTS:
self._qs = self.queryset.none()
return self._qs
# else STRICTNESS.IGNORE... ignoring
# start with all the results and filter from there
qs = self.queryset.all()
for name, filter_ in six.iteritems(self.filters):
value = self.form.cleaned_data.get(name)
if value is not None: # valid & clean data
qs = filter_.filter(qs, value)
self._qs = qs
return self._qs
如您所见，qs属性是通过遍历Filter对象列表，依次使初始qs依次通过每个对象并返回结果来解析的.参见qs = filter_.filter(qs, value)
As you can see, the qs property is resolved by iterating over a list of Filter objects, passing the initial qs through each of them successively and returning the result. See qs = filter_.filter(qs, value)
这里的每个Filter对象都定义了一个特定的def filter操作，该操作基本上采用Queryset，然后向其添加连续的.filter.
Each Filter object here defines a specific def filter operation, that basically takes teh Queryset and then adds a successive .filter to it.
这是BaseFilter类的一个示例
def filter(self, qs, value):
if isinstance(value, Lookup):
lookup = six.text_type(value.lookup_type)
value = value.value
else:
lookup = self.lookup_expr
if value in EMPTY_VALUES:
return qs
if self.distinct:
qs = qs.distinct()
qs = self.get_method(qs)(**{'%s__%s' % (self.name, lookup): value})
return qs
重要的代码行是:qs = self.get_method(qs)(**{'%s__%s' % (self.name, lookup): value})
因此，这两个前提条件为这个问题创造了完美的风暴.
So the two pre-conditions create the perfect storm for this issue.