Advanced elasticsearch query

Ad

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.

Ad

Answer

Ad

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.

Ad
source: stackoverflow.com
Ad