Multiple OR and AND conditions with Elasticsearch php

I would like to achieve the following query with ElasticSearch :

product_shipper = 1 AND ((product_type = product and price>0) OR (product_type = product_variation and price=0))

I've built the following query but it doesn't work, sending me an empty result :slight_smile:

                  "bool"=> [
                     "must"=> [
                        [
                           "bool"=> [
                              "should" => [
                                    "bool"=> [
                                        "must"=> [
                                            [ "match" => [ "product_type" => "product" ] ],
                                            [ 
                                                'range'=>['price'=>['gt'=>0]]
                                            ],
                                        ]
                                    ],
                                     "bool"=> [
                                        "must"=> [
                                            [ "match" => [ "product_type" => "product_variation" ] ],
                                            [ "match" => [ "price" => 0 ] ]
                                        ]
                                    ]
                              ]
                           ]
                        ],
                        [ 'match' => [ 'product_shipper' => $shipper ] ],
                     ]
                  ]
               ]

What am I doing wrong?

Start simple, add complexity, does this work by itself?

 [ 'match' => [ 'product_shipper' => $shipper ] ]

Thanks Rugenl for your answer.

Yes :slight_smile: it does.

I've also modified slightly the request to this :


               "query"=> [
                    "bool"=> [
                        "must"=> [
                            [
                            "bool"=> [
                                "should" => [
                                    [
                                        "bool"=> [
                                            "must"=> [
                                                [ "match" => [ "product_type" => "product" ] ],
                                                [ "range"=>['price'=>['gt'=>0]]],
                                            ]
                                        ],
                                        "bool"=> [
                                            "must"=> [
                                                [ "match" => [ "product_type" => "product_variation" ] ],
                                                ['range'=>['price'=>['lte'=>0]]]
                                            ]
                                        ]
                                    ]
                                ]
                            ]
                        ],
                        [ 'match' => [ 'product_shipper' => $shipper ] ],
                        ]
                    ]
               ]

But what happens is that I only have result for product_shipper = $shipper AND product_type = "product_variation" AND price <= 0

The other part of the should request is ignored, there is no product_type = "product" and prive > 0 in the results, and yes I checked there many of them in my dataset.

Any idea why it just analyzes the second part of my should clause ?

Comparing what you have to this answer, you have "should" => [ [ they have "should" => [ {

I wonder if the array of things keeps resetting the keys to the last value seen vs processing them as a list {}.

There is no {} in the php sdk only . Damn I feel so lost on this one. It's like I'm super close to get it right but no haha. So frustrating.

Any other idea Rugen ? Maybe the syntax I use within the should is not correct? I don't think there is an issue with the official php SDK for such a simple task. But there is so few documentation for it.

How would you write this query if you were to use the regular CURL syntax?

Yea, you've brought a good one :slight_smile: I'd suggest opening an issue on the git project for this.

Another thing to try is to switch the order of the "OR" items, just so see if it changes the results.

Yep, I just opened a ticket : Multiple Must within Should clause does not work · Issue #1152 · elastic/elasticsearch-php · GitHub

Let's see if I'm wrong or if there is an issue in the SDK, which I doubt but still ^^

Thanks for your help man :slight_smile: , if anyone else has something in mind, just let me know. I've been stuck for 2 days on this one.

Hello :slight_smile:

There was an issue in my syntax within the should clause...

Here is the proper code :

"query"=> [
    "bool"=> [
        "must"=> [
            [
                "bool"=> [
                    "should" => [
                        [
                            "bool"=> [
                                "must"=> [
                                    [ "match" => [ "product_type" => "product" ] ],
                                    [ "range"=>['price'=>['gt'=>0]]],
                                ]
                            ],
                        ],
                        [
                            "bool"=> [
                                "must"=> [
                                    [ "match" => [ "product_type" => "product_variation" ] ],
                                    ['range'=>['price'=>['lte'=>0]]]
                                ]
                            ]
                        ]
                    ]
                ]
            ],
            [
                'match' => [ 'product_shipper' => $shipper ]
            ],
        ]
    ]
]

Thanks a lot for your help :slight_smile: