Search code examples
phpmysqllaravelfull-text-searchsql-like

Issue with FullText or LIKE search (unable to find inventory)

Having some trouble finding an item in my inventory when a user searches for something that I know is in the inventory.

Table Columns are ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name

When I search Nikon Ti-E the script can't seem to find it. However, I know that it's in the inventory. In the database the Model_Name is listed as "Eclipse Ti-E w/HUBC Inverted Phase Contrast" and the ManufacturerNameMatch is 'Nikon'

I'm using FullTextSearch Index for multiple word searches, but that can't seem to find it so I've been messing with concatenating the columns and using a LIKE search (still nothing). Wondering if someone has some better ideas, or what I'm doing wrong or not understanding with FullTextIndex searches or LIKE searches with MySQL.

$category = 'Nikeon Ti-E'
$findPhraseMatch = DB::table('inventory')
                                ->selectRaw(DB::raw("*, GROUP_CONCAT(CambridgeID) as `CambridgeIDArray`, count(*) as `groupTotal`"))
                                ->where(DB::raw("CONCAT_WS(' ', ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name)"), 'LIKE', "%$category%")                                   
                                ->where('Units_OnHand', '>=', 1)                                    
                                ->groupBy('Model_Name', 'ManufacturerNameMatch')
                                ->orderBy('ManufacturerNameMatch')
                                ->paginate(15);

FullText Search here

// $final would look like +nikon +ti+e
$findFullMatch = DB::table('inventory')
                        ->selectRaw(DB::raw("*, GROUP_CONCAT(CambridgeID) as `CambridgeIDArray`, count(*) as `groupTotal`"))
                        ->whereRaw("MATCH(ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name, Title_Override, Description_Old) AGAINST ('$final' IN BOOLEAN MODE) AND Units_OnHand >= 1")
Solution

  • The like operator cannot find what you are looking for this way, since the concatenated text do not contain Nikeon Ti-E substring. There is also a spelling mistake, but this may only be misspelled here on SO.

    You would have to search for like '%Nikon% Ti-E%' (note the additional % between Nikon and Ti-E) if you wanted to use the like operator.

    Using fulltext search may be a more adequate solution for this. I would use the Boolean mode, in which you can specify that both words should be in the concatenated content in order to be present in the results.

    ... where match(ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name) against ('+Nikon +"Ti-E"' in boolean mode)