Find phrase with prefix with AWS SDK for PHP on CloudSearch

Question

In our backoffice we will use Cloudsearch for the search part instead of some Mysql request.

The problem is that I have some trouble to obtain the same result with Cloudsearch and I'd like some help if possible...

For example, if user search "Alexandre Call" :

With Mysql : one result for an event named "Alexandre & blablabla CallXXX"

For more information, the Mysql request use some ... WHERE CONCAT(FIELD1, " ", FIELD2, " ", FIELD 3) LIKE '%alexandre%' AND CONCAT(FIELD1, " ", FIELD2, " ", FIELD 3) LIKE '%call%' for my example.

With Cloudsearch : 280 results, with event that contain "Alexandre" OR event that contain "call" or word with suffixe "call + something"

This is how I use Cloudsearch with AWS SDK for PHP :

1/ I connect to my search domain :

$client = CloudSearchDomainClient::factory(array(
    'endpoint' => 'https://XXXXXXXX.eu-west-1.cloudsearch.amazonaws.com',
    'validation' => false,
    'version' => 'latest',
    'credentials' => [
        'key'    => _S3_API_KEY_,
        'secret' => _S3_API_SECRET_,
    ],
    'region' =>  'eu-west-1',
));

2/ My research :

$search_result['event'] = $client->search(
    array(
        // This is what user search, in my example $query = "Alexandre Call"
        'query'        => $query, 
        'queryOptions' => '{
                             "defaultOperator" : "or",
                             "fields":["description","nomevent^3", "idftpevent^2"]
                           }',
        'queryParser'  => 'simple',
        'size'         => 500,
        'start'        => 0
    )
);

With this I get 280 results as I said...any idea how I can obtains similar result that I have with mysql?

Edit :

I think I'd like to search something like this but I don't know how :

(and 
  (or description='alexandre' nomevent='alexandre' idftpevent='alexandre') 
  (or description='call' nomevent='call' idftpevent='call')
)

But impossible to make it works...the idea should be to have alexandre at least once in the 3 fields I search and same for call (for term like call, calling, callXXX...), any idea?

Edit 2 :

I tried the solution for my example:

$event_query = 'alexander* call*';

$search_result['event'] = $client->search(
    array(
        'query'        => $event_query,
        'queryOptions' => '{
                               "defaultOperator": "and",
                               "fields":["nomevent^3","idftpevent^2", "description"]
                           }',
        'queryParser'  => 'simple',
        'size'         => 500,
        'start'        => 0
    )
);

But I got no result...I did somehting wrong?

I have trouble to understand what "defaultOperator": "and", is used for? It means I search for alexandre* AND call* or it means I search for alexandre* and call* in the 3 fields I mention?

As I showed before, I'd like to search for alexandre* in one of the 3 fields I mention AND call* in at least one of the 3 fields I mention

Answer 1

I accept the other solution but I will still add what I really did if people want to see how it looks with AWS SDK for PHP :

1/ I get what user search and "clean" the search :

$_REQUEST['search'] = preg_replace(
    array('/\+/', '/-/', '/~/', '/>/', '/</', '/"/', '/\'/', '/\)/', '/\(/'), 
    "", 
    $_REQUEST['search']
);

2/ Then I explode the search to get each word one by one :

$word_list = explode(" ", trim($_REQUEST['search']));

3/ Now I can build my request :

// I start my query     
$event_query = "(and ";

// Now for each word, I add my condition :
// - I want each searched word in at least one of my 3 fields (nomevent, idftpevent and description)
// - I want the exact word or just a prefix
foreach ($word_list as $word) {
    $event_query .= "(or 
                       (prefix field=description '".$word."')
                       (prefix field=nomevent '".$word."')
                       (prefix field=idftpevent '".$word."')
                       (term field=description '".$word."')
                       (term field=nomevent '".$word."')
                       (term field=idftpevent '".$word."')
                     )";
}

// I close my query
$event_query .= ")";

4/ Now I just have to use AWS SDK for PHP to get my result :

$search_result['event'] = $client->search(
    array(
        'query'        => $event_query,
        'queryParser'  => 'structured',
        'size'         => 500,
        'start'        => 0
    )
);

$search_result['event'] = $search_result['event']->toArray();

And the result are in $search_result['event']['hits']['hit'], there is maybe a better way to get those hit but this way I achieve what I wanted !

Answer 2

I think I'd like to search something like this but I don't know how :

(and 
  (or description='alexandre' nomevent='alexandre' idftpevent='alexandre') 
  (or description='call' nomevent='call' idftpevent='call')
)

This is pretty close, but I think you can simplify this a lot by leveraging the built-in operators from the simple query parser. With that parser, prefix queries use * at the end of a word or phrase to indicate that you're searching for matches that begin with the preceding characters. So your query should look like one of the following:

/* Only treat the last word as a prefix */

alexandre call*

/* Treat each word as a prefix */

alexandre* call*

Now, to match the and logic of the compound query that you tried, you just need to change your defaultOperator to and (or remove that option, because and is the default).

Hope that helps!

Reference: AWS CloudSearch Documentation - Searching for Prefixes