Hi friends and developers, I really seek your help in getting this right. and thank you in advance
I am using nopcommerce 1.9 and it is working perfectly. However, I discovered that when I try to search for a product using the parent category, no products are displayed.
For example, If I have a product called "samsung" under Electronics > Smartphones. When type samsung in the Search Keywords, and I choose Smartphones in the Categories dropdown list, The product samsung will appear. However, if I select Electronics in the Categories Dropdown list, the Product samsung does not appear. Eventhough Samsung is under smartphones category which is under Electronics.
I hope my explanation is clear. and I would really appreciate your help in how to enhance the advanced search to be able to search for products using their parent categories.
Advance search problem
- 1
- 2
- 33
- 175
- 10/7/2009
- Canada
The problem is with the stored procedure Nop_ProductLoadAllPaged on the clause
I don't know if this is by design or not, but if you know some SQL you can do something before the INSERT INTO #DisplayOrderTmp statement like:
Then instead of the where clause above you'd do something like
Then after don't forget to drop the temporary table
This code may not work, I'm assuming you have some SQL knowledge and c# knowledge. If don't you'll have to ask someone who does.
WHERE
(
(
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
I don't know if this is by design or not, but if you know some SQL you can do something before the INSERT INTO #DisplayOrderTmp statement like:
SELECT *
INTO #CategoryIDs
FROM
(
SELECT @CategoryID as CategoryId
UNION
SELECT b.CategoryID
FROM Nop_Category a
JOIN Nop_Category b ON a.CategoryID = b.ParentCategoryID
WHERE a.CategoryID = @CategoryID
UNION
SELECT c.CategoryID
FROM Nop_Category a
JOIN Nop_Category b ON a.CategoryID = b.ParentCategoryID
JOIN Nop_Category c ON b.CategoryID = c.ParentCategoryID
WHERE a.CategoryID = @CategoryID
) as tmp
--repeat this pattern for as many levels you want to look at
Then instead of the where clause above you'd do something like
WHERE
(
(
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID IN (SELECT CategoryID FROM #CategoryIDs) AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
Then after don't forget to drop the temporary table
DROP TABLE #CategoryIDs
This code may not work, I'm assuming you have some SQL knowledge and c# knowledge. If don't you'll have to ask someone who does.
- 11
- 55
- 7/26/2011
- Bahrain
milasch wrote:
Hi milasch,
I have tried your code but it is not working, as when I add the statement SELECT CategoryID FROM #CategoryIDs, it displays and error that #CategoryIDs is invalid object name.
I would appreciate your help, and confirmation if the code you provided can work. I couldnt find any expert in SQL and C# which can help me urgently.
Thank you so much for your assistance.
The problem is with the stored procedure Nop_ProductLoadAllPaged on the clause
I don't know if this is by design or not, but if you know some SQL you can do something before the INSERT INTO #DisplayOrderTmp statement like:
Then instead of the where clause above you'd do something like
Then after don't forget to drop the temporary table
This code may not work, I'm assuming you have some SQL knowledge and c# knowledge. If don't you'll have to ask someone who does.
WHERE
(
(
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
I don't know if this is by design or not, but if you know some SQL you can do something before the INSERT INTO #DisplayOrderTmp statement like:
SELECT *
INTO #CategoryIDs
FROM
(
SELECT @CategoryID as CategoryId
UNION
SELECT b.CategoryID
FROM Nop_Category a
JOIN Nop_Category b ON a.CategoryID = b.ParentCategoryID
WHERE a.CategoryID = @CategoryID
UNION
SELECT c.CategoryID
FROM Nop_Category a
JOIN Nop_Category b ON a.CategoryID = b.ParentCategoryID
JOIN Nop_Category c ON b.CategoryID = c.ParentCategoryID
WHERE a.CategoryID = @CategoryID
) as tmp
--repeat this pattern for as many levels you want to look at
Then instead of the where clause above you'd do something like
WHERE
(
(
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID IN (SELECT CategoryID FROM #CategoryIDs) AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
Then after don't forget to drop the temporary table
DROP TABLE #CategoryIDs
This code may not work, I'm assuming you have some SQL knowledge and c# knowledge. If don't you'll have to ask someone who does.
Hi milasch,
I have tried your code but it is not working, as when I add the statement SELECT CategoryID FROM #CategoryIDs, it displays and error that #CategoryIDs is invalid object name.
I would appreciate your help, and confirmation if the code you provided can work. I couldnt find any expert in SQL and C# which can help me urgently.
Thank you so much for your assistance.
- 33
- 175
- 10/7/2009
- Canada
Try applying the changes to the stored procedure (!!!!not on the live environment!!!!).
I don't have how to test this right now and the code I gave was an approach that should be tried but it's far from being the best approach as it restricts the category levels to the number of union statements you manually put in the sproc. On my 1.80 customization I've replaced the stored procedure with a linq to entities statement.
If you still can't make it let me know and I'll take a quick look in a couple of hours.
I don't have how to test this right now and the code I gave was an approach that should be tried but it's far from being the best approach as it restricts the category levels to the number of union statements you manually put in the sproc. On my 1.80 customization I've replaced the stored procedure with a linq to entities statement.
If you still can't make it let me know and I'll take a quick look in a couple of hours.
- 11
- 55
- 7/26/2011
- Bahrain
milasch wrote:
Thank you so much. I have tried the same and it didn't work.
I will keep trying, I believe its good for me so I can learn more in SQL. Please let me know if you found any solution.
I really appreciate your help and time.
Nevermind my suggested approach. It doesn't work. If I make it work I'll let you know.
Thank you so much. I have tried the same and it didn't work.
I will keep trying, I believe its good for me so I can learn more in SQL. Please let me know if you found any solution.
I really appreciate your help and time.
- 1
- 2