How to use SQL AND condition in ElasticSearch query in PHP?

Question

I'm learning ElasticSearch by following this - https://qbox.io/blog/using-elasticsearch-in-e-commerce-part-1 article. Index & type for elasticsearch has been created by running following CURL command.

curl -XPOST 'localhost:9200/ecomercedata/gadgets/_bulk?pretty' -d'
{ "index": { "_id": 1 }}
{ "name" : "MacBook Pro", "category" : "Laptop", "brand" : "Apple", "rating" : 9, "prize" :  1299.00, "piecesSold" : 9500,  "dateOfRelease" : "2005-02-01"}
{ "index": { "_id": 2 }}
{"name" : "MacBook Air",  "category" : "Laptop", "brand" : "Apple",  "rating" : 8,  "prize" :  1099.00,  "piecesSold" : 8700,   "dateOfRelease" : "2006-05-01"}
{ "index": { "_id": 3 }}
{"name" : "ATIV Book",  "category" : "Laptop",  "brand" : "Samsung",  "rating" : 8,  "prize" :  1899.00,  "piecesSold" : 3500,   "dateOfRelease" : "2014-05-01"}
{ "index": { "_id": 4 }}
{"name" : "Inspiron",  "category" : "Laptop",  "brand" : "Dell",  "rating" : 6,  "prize" :  700.00,  "piecesSold" : 4600,   "dateOfRelease" : "2008-03-01"}
{ "index": { "_id": 5 }}
{"name" : "Ipad",  "category" : "Tablet",  "brand" : "Apple",  "rating" : 9,  "prize" :  600.00,  "piecesSold" : 9500 ,  "dateOfRelease" : "2005-07-01"}
{ "index": { "_id": 6 }}
{"name" : "Galaxy Tab",  "category" : "Tablet",  "brand" : "Samsung",  "rating" : 8,  "prize" :  550.00,  "piecesSold" : 8500 ,  "dateOfRelease" : "2007-07-01"}
{ "index": { "_id": 7 }}
{"name" : "Lumia",  "category" : "Mobile",  "brand" : "Nokia",  "rating" : 6,  "prize" :  50.00,  "piecesSold" : 12000 ,  "dateOfRelease" : "2009-03-01"}
{ "index": { "_id": 8 }}
{"name" : "Iphone",  "category" : "Mobile", "brand" : "Apple",  "rating" : 8,  "prize" :  60.00,  "piecesSold" : 28000 ,  "dateOfRelease" : "2002-03-01"}
{ "index": { "_id": 9 }}
{"name" : "Xperia",  "category" : "Mobile",  "brand" : "Sony",  "rating" : 8,  "prize" :  70.00,  "piecesSold" : 24000 ,  "dateOfRelease" : "2004-03-01"}'

Field mapping Script used-

curl -X PUT "http://localhost:9200/ecomercedata/gadgets/_mapping" -d '{
  "gadgets" : {
    "properties" : {
      "category" : {
        "type" : "String",
    "index" : "not_analyzed"
      },
      "brand" : {
        "type" : "String",
    "index" : "not_analyzed"
      },
      "name" : {
        "type" : "String"
      },
      "rating" : {
        "type" : "Integer"
      },
      "dateOfRelease" : {
        "type" : "date",
        "format" : "YYYY-mm-dd"
      },
      "prize" :  {
        "type" : "Double"
      },
      "piecesSold" : {
        "type" : "Integer"
      }
    }
  }
}'

I'm using PHPto fetch records from ElasticSearch. Here is my PHP Script to do that.

<?php
require 'vendor/autoload.php';

$hosts = [
    'http://localhost:9200',        // SSL to localhost
];

$client = Elasticsearch\ClientBuilder::create()     // Instantiate a new ClientBuilder
                    ->setHosts($hosts)              // Set the hosts
                    ->build();

$params = [
            'index' => 'ecomercedata',
            'type' => 'gadgets',
            'body' => [
                'query' => [
                        'constant_score' => [
                            'filter' => [
                                'bool' => [
                                    'must' => [
                                        'term' => [
                                            'category' => 'Laptop'
                                        ],
                                        'term' => [
                                            'brand' => 'Apple'
                                        ]
                                    ]
                                ]
                            ]
                        ]
                ]

            ]
        ];

try {
    $results = $client->search($params);
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
    exit;
}

echo '<pre>';
print_r($results);
echo '</pre>';
?>

Basically, I'm trying to extract all records where category=laptop and brand=Apple. But, it's not giving me a right number of records. According to entered dataset, I should get 2 records but I'm getting 4 records. Seems like, that category and brand condition works like as OR rather than AND.

I googled a lot. But, can't able to figure out what's I'm doing wrong.

Val · Accepted Answer · 2016-11-16 06:54:04Z

2

You need to wrap each term query in its own associative array, otherwise one gets overridden by the other. Try this query instead.

$params = [
        'index' => 'ecomercedata',
        'type' => 'gadgets',
        'body' => [
            'query' => [
                    'constant_score' => [
                        'filter' => [
                            'bool' => [
                                'must' => [
                                   [
                                    'term' => [
                                        'category' => 'Laptop'
                                    ]
                                   ],
                                   [
                                    'term' => [
                                        'brand' => 'Apple'
                                    ]
                                   ]
                                ]
                            ]
                        ]
                    ]
            ]

        ]
    ];

answered Nov 16, 2016 at 6:54

Val

218k14 gold badges377 silver badges384 bronze badges

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

1 Comment

Suresh Over a year ago

Yeah, this one is working. Got my mistake. Thanks for this help.

Sandyandi N. dela Cruz · Accepted Answer · 2016-11-16 06:58:43Z

2

bool should ALWAYS be wrapped in query wherever it is placed. Also, your term queries there is incorrect. They should be in their own arrays like so:

[
    'constant_score' => [
        'filter' => [
            'query' => [
                'bool' => [
                    'must' => [
                        [
                            'term' => [
                                'category' => 'Laptop'
                            ]
                        ],
                        [
                            'term' => [
                                'brand' => 'Apple'
                            ]
                        ]
                    ]
                ]
            ]
        ]
    ]
]

edited Nov 16, 2016 at 6:58

answered Nov 16, 2016 at 6:50

Sandyandi N. dela Cruz

1,34510 silver badges20 bronze badges

3 Comments

Suresh Over a year ago

Do u mean bool should be inside query ?

Val Over a year ago

constant_score/filter is the old way but it is still correct. The problem is different, see the other answer ;-)

Suresh Over a year ago

Yeah, according to this doc - elastic.co/guide/en/elasticsearch/guide/current/… it's fine.

Collectives™ on Stack Overflow

How to use SQL AND condition in ElasticSearch query in PHP?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related