[SOLVED] -- [Attributs / Order / StoredProcedure]

Hi all,

We have bought Nop Beauty Responsive Templates, and before to install it, we have an order attributes by number of product who contains this attribute.
[Sample]
With :
Product_1 contains size : XS, S, L, XL
Product_2 contains size: S, XL, XXL
Product_3 contains size: S,L, XXL
attributs will be display like that :
size :
1. S (3)
2. L (2)
3, XXL (2)
4. XL (2)
5, XS (1)
[/Sample]

To do this we had edit : ¨ProductLoadAllPaged" and replace:
this
    INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
    SELECT DISTINCT [psam].SpecificationAttributeOptionId
    FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
    WHERE [psam].[AllowFiltering] = 1
    AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
by that
    INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
    SELECT [psam].SpecificationAttributeOptionId
    FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
    WHERE [psam].[AllowFiltering] = 1
    AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
    group by [psam].SpecificationAttributeOptionId
    order by count([psam].ProductId) desc

But with NopAjaxFilters this order is not keep because NopAjaxFilters use a new procedure with encryption. So we have find this procedure without encryption to edit it, again (it's was not easy to do this but we have do it).

In your procedure we have try to do that, replace:
that
    --INSERT INTO #FilterableProductVariantIdsDistinct ([ProductVariantAttributeId])
    --SELECT DISTINCT ProductVariantAttributeId
    --FROM #FilterableProductVariantIds
by that
    INSERT INTO #FilterableProductVariantIdsDistinct ([ProductVariantAttributeId])
    SELECT ProductVariantAttributeId
    FROM #FilterableProductVariantIds
    GROUP BY ProductVariantAttributeId
    ORDER BY count(ProductId).

But order not working whatever we edit, this list is only order by Name. (i have try to manufacturer, but it's the same things).

We Need Help.

PS : Why do you Encrypt your StoredProcedure ??

Please note that the ProductVariantAttributeId is actually the id of the mapping between a product and an attribute. So when the filter attributes are being built, every attribute has several ProductVariantAttributeIds attached to it. So you do not need to change the stored procedure to sort the attributes by number of products for each attribute. You can do this sorting in the Razor view of the attribute filters:

AttributeFilter.cshtml

before this line:

@foreach (AttributeFilterItem attributeFilterItem in attributeFilterGroup.FilterItems)

sort the attributeFilterGroup.FilterItems like this:

attributeFilterGroup.FilterItems.OrderBy(x => x.ProductVariantAttributeIds.Count)

Let me know if this works for you!

Hi,

Thank you for your answer, it's working very well with Attributs.

But, i want doing the same thing with SpecificationAttributesOption (my color) and count is not available with.

By default,  the list returned by 'ProductLoadAllPaged' is good order and when i not use NopAjaxFilter.
But when NopAjaxFilter is enable my list is not order like i want.

I know for it, normaly we use 'DisplayOrder' but my color need to be order with the current product list. And its possible on my categoriy one Black is in more  product than blue but on category two Blue is in more product than Black.

Explain :

Categorie 1 :
-- Product 1 : Color is Black
-- Product 2 : Color is Blue
-- Product 3 : Color is Blue

Categorie 2 :
-- Product 5 : Color is Black
-- Product 2 : Color is Blue
-- Product 4 : Color is Black
I would have
Categorie 1
Color :
- Blue
- Black
Categorie 2
Color :
- Black
- Blue

But i always have
Color :
-Black
-Blue


In "Nop.Web.Models.CatalogPagingFilteringModel" I've already try to replace
SpecificationAttributeOptionDisplayOrder = sao.DisplayOrder,
SpecificationAttributeOptionDisplayOrder = sao.ProductSpecificationAttributes.Count,

In SpecificationFilter.cshtml

replace  
@foreach (SpecificationFilterItem specificationFilterItem in specificationFilterGroup.FilterItems)
By
@foreach (SpecificationFilterItem specificationFilterItem in specificationFilterGroup.FilterItems.OrderByDescending(x => x.DisplayOrder))

And in SpecificationAttributeService
replace

      return _specificationAttributeOptionRepository.GetById(specificationAttributeOptionId);

By

var _SpecificationAttributeOption = _specificationAttributeOptionRepository.GetById(specificationAttributeOptionId);
            _SpecificationAttributeOption.DisplayOrder = _SpecificationAttributeOption.ProductSpecificationAttributes.Count;
            return _SpecificationAttributeOption;