在Django的筛选查询中如何同时过滤两个不同列的数值?

huangapple go评论65阅读模式
英文:

How to filter two different columns values together in filter query Django?

问题

我有两列,

对于单个数值 amount 和范围数值 amount_minamount_max

amount amount_min amount_max
25000 0 0
0 100000 300000

使用以下查询,

Jobs.objects.filter(Q(created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30)),Q(amount__range=(100000, 300000)) | Q(amount_min__gte = 100000, amount_max__lte = 300000))

以下是观察到的输出,

amount amount_min amount_max
25000 0 0
0 100000 300000

然而,预期输出应该是,

amount amount_min amount_max
0 100000 300000

有人可以帮我解决这个查询吗?

英文:

I have two columns,

For single amount value amount and for range amount values amount_min and amount_max

amount amount_min amount_max
25000 0 0
0 100000 300000

Using the following query,

Jobs.objects.filter(Q(created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30)),Q(amount__range=(100000, 300000)) | Q(amount_min__gte = 100000, amount_max__lte = 300000))

Following is the observed output,

amount amount_min amount_max
25000 0 0
0 100000 300000

However the expected output should be,

amount amount_min amount_max
0 100000 300000

Can anyone please help me in this query ?

答案1

得分: 2

你可以使用Q对象来结合条件并使用OR运算符(|)。

试试这个:

from django.db.models import Q
import datetime

Jobs.objects.filter(
    created_on__gte=datetime.datetime.now() - datetime.timedelta(days=30),
    Q(amount_min=0, amount_max=0) |
    Q(amount_min__gte=100000, amount_min__lte=100000, amount_max__gte=300000, amount_max__lte=300000) |
    Q(amount__range=(100000, 300000))
)
英文:

You can use the Q object to combine the conditions with OR operator (|).

Give this a try:

from django.db.models import Q
import datetime

Jobs.objects.filter(
    created_on__gte=datetime.datetime.now() - datetime.timedelta(days=30),
    Q(amount_min=0, amount_max=0) |
    Q(amount_min__gte=100000, amount_min__lte=100000, amount_max__gte=300000, amount_max__lte=300000) |
    Q(amount__range=(100000, 300000))
)

答案2

得分: 0

我认为你在处理 Q 方面遇到了问题,因为它不喜欢与普通命名参数结合使用。

命名参数示例:created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30)

我个人的做法是过度包装 Q 语句并非常谨慎,例如:

Jobs.objects.filter(
    Q(created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30))
    & Q(
      Q(
          Q(amount_min__gte = 100000)
          & Q(amount_min__lte = 100000)
          & Q(amount_max__gte = 300000)
          & Q(amount_max__lte = 300000)
      )
      | Q(amount__range=(100000, 300000))
    )
)

是否有点过度?- 可能。

英文:

I think your running into issues with Q because it doesn't like being combined with normal named arguments

Named argument example: created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30)

What I personally do is over-wrap Q statements and be very deliberate, ex:

Jobs.objects.filter(
    Q(created_on__gte = datetime.datetime.now() - datetime.timedelta(days=30))
    & Q(
      Q(
          Q(amount_min__gte = 100000)
          & Q(amount_min__lte = 100000)
          & Q(amount_max__gte = 300000)
          & Q(amount_max__lte = 300000)
      )
      | Q(amount__range=(100000, 300000))
    )
)

Overkill?- Probably.

huangapple
  • 本文由 发表于 2023年3月31日 03:10:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/75892096.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定