Advanced elasticsearch query

Question

I am using laravel 4.2, mongodb and elasticsearch. Below is a working code, I am trying to convert this advanced where queries to elasticsearch queries:

$products = Product::where(function ($query) { 
            $query->where (function($subquery1){ 
                $subquery1->where('status', '=', 'discontinued')->where('inventory', '>', 0); 
                }); 
            $query->orWhere (function($subquery2){
                $subquery2->where('status', '<>', 'discontinued'); 
                });                         
        })->get();      

All I can get so far is just returning discontinued products, the code below works but it is not what I need:

$must = [
               ['bool' => 
                    ['should' => 
                        ['term' => 
                            ['status' => 'discontinued']                            

                        ]                       
                    ]   
                ]               
            ];

Can you show me how can I achieve the same query I first described above but in elasticsearch? I want to return discontinued products with inventory, then also return products that are not equal to discontinued.

Answer 1

The WHERE query you've described can be expressed in SQL like this

... WHERE (status = discontinued AND inventory > 0)
       OR status <> discontinued

In Elasticsearch Query DSL, this can be expressed like this:

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "status": "discontinued"
                    }
                  },
                  {
                    "range": {
                      "inventory": {
                        "gt": 0
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must_not": [
                  {
                    "term": {
                      "status": "discontinued"
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Translating this query into PHP should now be straightforward. Give it a try.