SQL Query to change Specification type

Hi Guys
I have nearly 11,000 products in a NopCommerce 3.9 database (as imported by a NopCommerce gold partner here).

Time restrictions are putting the pressure on me so the last part of the set up is dragging on and I cannot launch the website after nearly 2 years.

I have a NopTemplates theme installed (Traction) which uses an Advanced Ajax filter, and one of the filters I need to use utilises a product specification attribute, BUT, the specification attribute has been entered as a "custom text" type specification instead of an "option" type specification (and also NOT set as "Allow Filtering") that would trigger the filter. This is true on all of the products and as you can imagine going through 11,000 products and deleting and then adding the specification again as the correct type would just not be viable.

Is there an SQL query that could be run across the database that could change the specification (the specification is called "Layout" in all instances and all currently configured as "Custom text" type) from Custom Text to Option and then make "Allow Filtering" for that specification as True instead?

I am thinking the data may have been added slightly incorrectly if the current "Custom text" specification is showing as "Allow Filtering" with an X (False) as Custom Text option disables the option to allow filtering when selected when adding a new specification.

Here is how they sit currently:

First of all, even if the you select Custom Text type, (and nopCommerce hides the checkbox for filtering), it still saves the column value as False in the database, and therefore that cross sign in the list. It's not editable for Custom Text type for obvious reasons.

Now about the sql query thing, there will be a couple of things to notice here.

To change the specification attribute type to Option with a query is certainly possible, however you'll need to first verify if SpecificationAttributeOptionId column in Product_SpecificationAttribute_Mapping table actually contain the correct value for those specifications. By default it selects first available value in the list and that option id is set. Because lot of products were imported with Custom Text, I think they all might be storing the first available value.

What happens for a Product Specification is that, either Selected Option Value is shown or CustomValue is shown depending on the attribute type, therefore it'd require some good analysis of the database before a query can be written.

Hope that helps.
Best Regards,
Anshul

Fantastic reply - thank you so much!
Not knowing the structure and procedures is my main problem.

Based on what you said I checked both of those tables to see the data. Here are the results:





As you can see in the first table on row 4 I changed the product specification manually in the NopCommerce admin to an option with filtering, hence why that one shows as Allow Filtering 1 (true). It also seems as though all attributes defaulted to the first ID of 10 which must be custom text yea?

You can also see in the second table that the "Layout" specification (the one I need to be an Option with Filtering) has an ID of 508058 which is then reflected in the first table in row 4 ("OEM Replacements" is a layout option type). but all other instances (where it says OEM Replacements) seem to have different IDs that are not listed in the main Specification Attribute ID list. How has this happened?

Does this answer any of the questions you had and does it give an kind of solution on a query?

Would there be an SQL query that could change all entries marked "OEM Replacements" and "Full Length Race" (these are the two options for the 'Layout' specification" to the correct ID and type? I am not sure what they are or how you would write it, hence my question.

Well, I'm not sure how those values came. Only the person who imported it would be able to answer that better.

Now to fix things up, I would do the following.

1. Backup current database.
2. Check if there are two entries "OEM Replacements" and "Full Length Race" in SpecificationOption table with SpecificationAttributeId = 508058. If not, add them. Note their IDs. (say x and y respectively)

3. Run Query # 1

UPDATE Product_SpecificationAttribute_Mapping SET SpecificationAttributeOptionId=x, AllowFiltering=1, CustomValue=NULL WHERE CustomValue='OEM Replacements'

4. Run Query # 2

UPDATE Product_SpecificationAttribute_Mapping SET SpecificationAttributeOptionId=y, AllowFiltering=1, CustomValue=NULL WHERE CustomValue='Full Length Race'

5. Check if things work as you think they should.

Note: The current id of 508058 that you see in the [Product_SpecificationAttribute_Mapping] table is not that of SpecificationAttribute table but of SpecificationAttributeOption table.

Hope that helps.
Best Regards,
Anshul

Sorry for the delayed reply Anshul, long weekend in Australia!

Mate, I can't thank you enough. That seems to have fixed all my issues with the Ajax filter.

One thing it did do was stop the actual specification type showing in the Specification Tab List of the product detail page (it is just a blank specification now).

All the entries in Product_SpecificationAttribute_Mapping with the SpecificationAttributeOptionID of 508058 (OEM Replacements) or 508062 (Full Length Race) now has a Custom Value of "NULL", but as the short description is the same as the Layout Type I should get away with it.

It didn't take much to work out that a reshuffle of the Query you gave me put all the correct Values back in the NULL spaces. Thanks again, all fixed now.