Hello,
The Product_Category_Mapping table has a DisplayOrder column that should be used when selecting sort by 'Position' within a category. Instead, the Product's DisplayOrder value is being used (default behaviour) within the ProductExtensions static class.
The Admin\Category\Edit\Products tab behaves correctly and displays the products in the order assigned to the DisplayOrder field. This assigned DisplayOrder is expected when viewing the Category and its Product(s).
Therefore, a solution would be to have 'Sort By' enum values for sorting by Product DisplayOrder and Product_Category_Mapping DisplayOrder. Alternatively, the 'Position' enum should be used to sort by Product_Category_Mapping DisplayOrder only. The ProductExtension class only extends the Product object and the Product_Category_Mapping DisplayOrder is not available.
The problem is with the 'join' on the tables, the approaches are different and result in different as can be viewed below:
ProductService::public virtual async Task<IPagedList<Product>> SearchProductsAsync(...
Select
...
[p].[ParentGroupedProductId],
[p].[ProductTypeId]
FROM
[Product] [p]
WHERE
[p].[Published] = 1 AND [p].[Deleted] = 0 AND EXISTS(
SELECT
*
FROM
[Product_Category_Mapping] [pc]
WHERE
[pc].[IsFeaturedProduct] = 0 AND [pc].[CategoryId] IN (2763) AND
[pc].[ProductId] = [p].[Id]
)
and CategoryService::public virtual async Task<IPagedList<ProductCategory>> GetProductCategoriesByCategoryIdAsync...
SELECT
[pc].[Id],
[pc].[DisplayOrder],
[pc].[IsFeaturedProduct],
[pc].[CategoryId],
[pc].[ProductId]
FROM
[Product_Category_Mapping] [pc]
INNER JOIN [Product] [p] ON [pc].[ProductId] = [p].[Id]
WHERE
[pc].[CategoryId] = @categoryId AND [p].[Deleted] = 0
ORDER BY
[pc].[DisplayOrder],
[pc].[Id]
Reproduce the steps by assigning all Products a DisplayOrder of 0 (zero). Within Admin Area, edit a Category, add Products and assign positve DisplayOrder numbers. The Products will be sorted correctly in the Admin Category. Clear Cache\Restart App. Preview category and confirm products not in expected assigned order.
Kind regards,