mi6crazyheart mi6crazyheart - 17 days ago 6
PHP Question

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

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 Val
Answer

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'
                                    ]
                                   ]
                                ]
                            ]
                        ]
                    ]
            ]

        ]
    ];
Comments