phpyii2boolean

BOOLEAN and LIKE search together with Yii2?


I use YII2 Framework and I've built this search in BOOLEAN MODE:

if( $campi[$i] == "PossessoreElenco" ){
          if(strpos($valor[$i], ' OR ') !== false) {
                $titOR = str_replace(" OR ", ' ', $valor[$i]);
          $query.=  'MATCH(PossessoreElenco) AGAINST("'.$titOR.'" IN BOOLEAN MODE)'; }

Now, if I write "Marc*" the result show both this : "Marco", "San Marco". This is right, but is not the result that I want. I would to take only the result that STARTS with the word that I write. So, at the end if I write Marc* OR Mich* in BOOLEAN MODE, I want to search for the result that STARTS with "Marc" or "Mich" (example 'Marco' or 'Michele') and not all results that CONTAINS the words (example, I don't want 'San Marco'). There is an opportunity to implement this option mantaining the boolean search? I can use LIKE 'Marc%', but in this solution I lose the boolean search. Thank you for the help!


Solution

  • The Boolean Full-Text Searches is based on word and don't care for the beginning of the string .. if you need this you could enforce the query adding an having cluase fro filter the risulting rows

     WHERE MATCH(PossessoreElenco) AGAINST( ?  IN BOOLEAN MODE) 
     HAVING PossessoreElenco LIKE caoncat(?,'%')
    

    NB The use of an having clause for filter the result without aggregation function is pretty improper

    So you could use your main query as a subquery for apply the like eforcemnet

    select *
    from (
        SELECT 
        ....
        WHERE MATCH(PossessoreElenco) AGAINST( :my_word  IN BOOLEAN MODE) 
    
    ) T
    WHERE T.PossessoreElenco LIKE concat(:my_word,'%')
    

    You should avoid the use of php var in sql because this can produce sqlijcetion for avoid tgis you could use named param and use the related binding function provided by Yii2