How to implement logical AND and OR operator in one query using django

Question

I am looking to implement AND and OR logic in my Django query but after checking a lot of posts and searching I found that I can not implement AND-OR logic in the same query filter.

Here is what I want to achieve

I want to fetch records which have key ABC AND values Value1 OR Values2 OR Values3 AND key CDE AND values Value4

I am using python3.6, django1.11, mongoengine, and MongoDB

filter =  [ { "Key": "ABC", "Values": ["Value1", "Value2", "Value3"]}, { "Key": "CDE", "Values": ["Value4"]} ]

Response:

[  
    {
        "_id" : ObjectId("5ebd29286310619f046ba866"),
        "account_id" : "12",
        "key" : "ABC",
        "Value" : "Value1"
    }

    {
        "_id" : ObjectId("5ebd29286310619f046ba866"),
        "account_id" : "12",
        "key" : "ABC",
        "Value" : "Value2"
    }

    {
        "_id" : ObjectId("5ebd29286310619f046ba866"),
        "account_id" : "12",
        "key" : "ABC",
        "Value" : "Value3"
    }

    {
        "_id" : ObjectId("5ebd29286310619f046ba866"),
        "account_id" : "12",
        "key" : "CDE",
        "Value" : "Value4"
    }

]

This is what I have already tried but got an error mongoengine.errors.InvalidQueryError: Cannot resolve field "key"

from mongoengine.queryset.visitor import Q
records = MyModel.objects.get(Q(key__exact='ABC'),
    Q(Value__exact='Value1') | Q(Value__exact='Value2') | Q(Value__exact='Value3', Q(key__exact='CDE'), Q(Value__exact='Value4')))

Deniz Kaplan · Accepted Answer · 2020-05-18 07:17:05Z

1

You can use Q object, which is mentioned at docs. As an example:

Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)

this code equivelent to:

SELECT * from polls WHERE question LIKE 'Who%'
    AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

answered May 18, 2020 at 7:17

Deniz Kaplan

1,6091 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vikas Gautam Over a year ago

Hi @Deniz Thanks for the response, I have tried this but I got an error.

Vikas Gautam Over a year ago

I have added code that I tried, please note that I am using mongoengine Q and Django Q

Deniz Kaplan Over a year ago

I think is is about capital 'K' of 'Key', can you try with Q(Key__exact='CDE')?

Vikas Gautam Over a year ago

But I DB I have field key and Key, I have tried this also it is not working,

Deniz Kaplan Over a year ago

As the error indicates, there is a mismatch about key field. Maybe your model definition and database scheme not same. It may cause this error. This answer may help you: stackoverflow.com/a/25515226/2474573

|

Collectives™ on Stack Overflow

How to implement logical AND and OR operator in one query using django

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related