Adding Manufacturer Search to Product Search (dbo.ProductLoadAllPaged)

I am trying to add Manufacturer Names to the product search. I can't figure out why my changes aren't adding Manufacturer Names to the search query. Can anyone review my changes and help me figure out why these changes aren't doing what they should?

What I have done:
- Modified dbo.nopCommerce > Stored Procedures > dbo.ProductLoadAllPaged
- Upon finishing the modification, I executed the query (to update the Stored Prodcedure)
- Cleared the store's cache and restarted the application to ensure changes made it to the site

My changes to dbo.ProductLoadAllPaged:
https://gist.github.com/DerekFoulk/afe92591ad72d63f5abaee85ba55b45b

Summary of changes:
- Added '@SearchManufacturer' variable (bit - default = 0)
- Set '@SearchManufacturer = 1' on line 66
- Added the following 'UNION' on line 266:

IF @SearchManufacturer = 1 
BEGIN 
    SET @sql = @sql + ' 
    UNION 
    SELECT pmm.ProductId 
    FROM Manufacturer m with (NOLOCK) 
    JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) 
        ON pmm.ManufacturerId = m.Id 
    WHERE ' 
    IF @UseFullTextSearch = 1 
        --Toggle the below lines if you index these tables (Full-Text) 
        --SET @sql = @sql + 'CONTAINS(m.[Name], @Keywords) ' 
        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 ' 
    ELSE 
        SET @sql = @sql + 'PATINDEX(@Keywords, m.[Name]) > 0 ' 
END 

The above query returns the expected 'ProductId's when ran separately (from the Stored Procedure). Also, the search runs without throwing any exceptions. So, not sure what the problem is... Any help would be greatly appreciated!

Is there a better way of doing this? I do not want to add the manufacturers' names to the products' names nor the products' tags (which I saw mention of in another post). Also, I'd prefer not to rely on someone else's plugin for this, as it seems like this should not be too hard to do lol.

As usual, any help is greatly appreciated!

I just realized that categories are not searched either. If you open the store demo, and search for "Apparel", you get no results. Every product in the 'Apparel' category should be listed on the search results page when this search is performed.

So, in addition to adding the Manufacturer (name) to the queried values, I would also like to know the recommended way of querying categories (names) as well.

Can anyone share what they've done to modify the search feature?

1) Modify Procedure
IF @SearchManufactures = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT pmm.ProductId
      FROM Manufacturer manufacture with (NOLOCK)
      Inner JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON pmm.ManufacturerId = manufacture.Id
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(manufacture.[Name], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, manufacture.[Name]) > 0 '
END
2) Modify _productSearvice SearchProducts method and you need to pass SearchManufactures  parameter same as done for other parameters.  like @SearchDescriptions .

The above code is working for me

pankajtiwari wrote:

Thank you for sharing this! I will give this a shot. Just in time for our deployment lol- thank you sir!

Works like a charm!