Search with "AND" instead of "OR"

Question

Hi,

I'm stuck with a search problem and haven't found any solution reading the forum.

I added a search field in my ChronoConnectivity connection using that line in List Display :

<input type="text" id="filter_search" name="srch" placeholder="Search..." class="form-control" />

And in my model settings, in "Searchable" I put :

myModel.title
myModel.details

The search works great but when I search for instance "word1 word2" in my search field and display the debug log it shows :

[...]WHERE (
(`myModel`.`title` LIKE '%word1%') OR
(`myModel`.`details` LIKE '%word1%') OR
(`myModel`.`title` LIKE '%word2%') OR
(`myModel`.`details` LIKE '%word2%')
)[...]

I'd like to override this to search "word1 AND word2" instead of "word1 OR word2", i.e :

[...]WHERE (
((`myModel`.`title` LIKE '%word1%') OR (`myModel`.`details` LIKE '%word1%'))
AND
((`myModel`.`title` LIKE '%word2%') OR (`myModel`.`details` LIKE '%word2%'))
)[...]

Because when I search "word1" if I got 20 results, I want to reduce search results adding another word to aim relevant content (word1 AND word2) instead increasing results because it looks for "word1 OR word2 giving me for instance 50 results.

Any advice on how to override this part of the SQL ?

Thanks in advance for any help you are able to provide,

Arnaud

errno · Accepted Answer

I found a solution for my problem🙂
If anyone got the same problem I'll paste here the solution I used :

I changed the classic search field in my ChronoConnectivity connection (in List Display) from :

<input type="text" id="filter_search" name="srch" placeholder="Search..." class="form-control" />

To :

<input type="text" id="filter_search" name="fltr[myModel][srch]" placeholder="Search..." class="form-control" />

This way, the search field become a classic filter.

In my model settings, in "Searchable" I removed everything (because I don't use anymore the classic search field).

Then in "Models" > "Conditions" I coded the search filter my way using :


$dbo = \GCore\Models\myModel::getInstance()->dbo;

//If using the submit button, retrieve the search field content with POST, else retrieve the search field content with SESSION (because you are probably using pagination) :

if(isset($_POST['submit']) && $_POST['submit'] == 'Search') {
    if(isset($_POST["fltr"]["myModel"]["srch"])) {
        $srch = $_POST["fltr"]["myModel"]["srch"];
    }
} elseif (isset($_SESSION['gcore']['GCore\Extensions\Chronoconnectivity\Controllers\Lists']['myModel']['filters']['myModel']['srch'])) {
    $srch = $_SESSION['gcore']['GCore\Extensions\Chronoconnectivity\Controllers\Lists']['myModel']['filters']['myModel']['srch'];
}

//Now $srch contains the search field content

if($srch){
    $req_srch = array();
    
    //Explode the search field in words
    $search_words=explode(' ', $srch);

    //For each word, trim it and escape it to avoid sql injections, then, construct your custom sql with AND instead of OR
    foreach($search_words as $word) {
        $word = trim($word);
        $word = $dbo->quote('%'.$word.'%');
        $req_srch[] = "(myModel.titre LIKE {$word} OR myModel.detail LIKE {$word})";
    }
    $return_array[] = ":(".implode(" AND ", $req_srch).")";
}

//Return your custom filter

return $return_array;

Now my search field correctly do :

[...]WHERE (
((`myModel`.`title` LIKE '%word1%') OR (`myModel`.`details` LIKE '%word1%'))
AND
((`myModel`.`title` LIKE '%word2%') OR (`myModel`.`details` LIKE '%word2%'))
)[...]

Forums

Search with "AND" instead of "OR"