Ted Krapf Consulting

NopCommerce - Search Product Attributes

Date: 6/15/2020 10:08:42 PM

It's late and I want to still get some Xbox in for before my caffeine runs out for the day (Doom Eternal is calling me to finish it tonight), so let's cut straight to the chase -- NopCommerce lacks a Product Attribute search inclusion, so this is a quick change to add it to the search.

Please note, this was done against NopCommerce 4.20, your mileage may vary based on your version.

• In the site's Administration dashboard, go into Configuration and then All Settings.  Add a new custom setting called "catalogsettings.productsearchincludeproductattributes", and set it's value to "True".
   
• Crack open SQL Server Management Studio (or your tool of choice) and open the stored procedure "dbo.ProductLoadAllPaged".
   
• Jump down to about line 156 (after "--localized product name", and before "IF @SearchDescriptions = 1") and drop in the following SQL:
  
          ---------------------------------------------------------------------------------------------------------------------------------------------
          ---------------------------------------------------------------------------------------------------------------------------------------------
          --added by TED 2020-06-15 - START
          --Adds Product Attribute (name) searching
          IF (EXISTS (SELECT TOP(1) Id FROM dbo.Setting WHERE name='catalogsettings.productsearchincludeproductattributes' AND value='True'))
          BEGIN
              SET @sql = @sql + '
              UNION
              SELECT
                          pam.ProductId
              FROM        dbo.ProductAttributeValue            pav
                  JOIN    dbo.Product_ProductAttribute_Mapping pam ON pam.Id = pav.ProductAttributeMappingId
              WHERE '
              IF @UseFullTextSearch = 1
                  SET @sql = @sql + 'CONTAINS(pav.Name, @Keywords) '
              ELSE
                  SET @sql = @sql + 'PATINDEX(@Keywords, pav.Name) > 0 '
          END
          --added by TED 2020-06-15 - END
          ---------------------------------------------------------------------------------------------------------------------------------------------
          ---------------------------------------------------------------------------------------------------------------------------------------------
   
• Execute your SQL to update the stored procedure
   
• Give it a test on the live site (I found that I didn't need to Clear Cache or Restart Application) for the new search feature to work.        

As an added bonus, I also realized that the site's Search Auto-Complete worked as well for the Product Attributes.

Happy coding and take care!