10 Antworten zu diesem Thema

[nickh]

Hi all,

 

A lot of products on my site require the same attributes (roughly 3 drop down lists with about 10 items in each list).  I can't see any way of creating these as "templates" so that I don't have to re-type each one on each product.

 

Is this possible, or will I have to write a DB script to replicate the attributes across all the products?

 

Thanks,

Nick

[Michael Herzog]

Hi Nick,

 

to manage attributes the way you described it will be one of the features of the next version of SmartStore.NET.

 

Rgeards

[nickh]

Hi Michael,

 

I see.  Will this functionality be released soon?...I need to put this site live within the next few weeks, and I can't do so without adding these drop down list options.

 

I have just had a look at the DB and ran a script to insert rows into [dbo].[Product_ProductAttribute_Mapping].  This was easy and made sense.

 

...but this did not work as it seems that each Attribute must have its own separate list of attribute values in [dbo].[ProductVariantAttributeValue] table.  I could write another script to do this, but it means that this table will have about 50000+ rows in it.  Will this become an issue for performance?  Is there no other way that I could share these attribute values?

 

Thanks,

Nick

[Marcus Gesing]

Well, new attribute structure is not coming in the next few weeks. It will be a bigger bunch of changes.

You are right, at the moment every product has its own attribute values persisted. But the performance

drawback should be ok because they are loaded per product on the product detail page. I would give your 

script "solution" a try. I do not know any other way of sharing the data at the moment.

[nickh]

Well I was wondering if it would be worth making a code change to allow us to flatten the DB design?  Is this feasible, and if so please could you point me in the right direction in the code?

[Marcus Gesing]

That's complex and it's not worth it. What I'm thinking of is extending the existing system by

"attribute sets" in the way that the user can use both, sets and values per product.

[nickh]

Sounds good to me.  Do you know roughly how long it'll be before we see these features in a release?

[Marcus Gesing]

Maybe summer this year. It is planned for .NET 2.5.

But it was planned for a previous version too but never found the time to implement it.

[nickh]

Ok, thanks for the information.

 

Are either of you able to help me with the necessary SQL for a work-around solution?  I have a row in [dbo].[Product_ProductAttribute_Mapping] which has 70 corresponding rows in [dbo].[ProductVariantAttributeValue] (it's a LOOOOONG drop down list of attributes!).  I have 240 other products that need the exact same drop down list options, so I have to insert these into [dbo].[Product_ProductAttribute_Mapping] (as mentioned above).  Now here is the bit I need help with:

For each new row in [dbo].[Product_ProductAttribute_Mapping], I need to insert 70 rows into [dbo].[ProductVariantAttributeValue] by copying the existing items in [dbo].[ProductVariantAttributeValue], using the corresponding ID from [dbo].[Product_ProductAttribute_Mapping] for each row (meaning adding 16800 rows in [dbo].[ProductVariantAttributeValue]).

 

I hope this makes sense.  Are you able to help?

 

Thanks,

Nick

[Marcus Gesing]

Yes. ProductVariantAttributeValue requires ProductVariantAttributeValue.ProductVariantAttributeId which references

Product_ProductAttribute_Mapping.Id.

[nickh]

Hi Marcus,

 

I understand this, but what I was hoping for was help with the actual query!

 

...however I think I've done it!  Here it is.  I hope it will help other people trying to do the same thing:

		with ExistingItems as (
				select mapping.Id, val.ProductVariantAttributeId, val.Alias, val.Name, val.ColorSquaresRgb, val.PriceAdjustment, val.WeightAdjustment, val.IsPreSelected, val.DisplayOrder, val.ValueTypeId, val.LinkedProductId, val.Quantity
				from [dbo].[Product_ProductAttribute_Mapping] mapping
				INNER JOIN 
				(
					select * from [dbo].[ProductVariantAttributeValue] where ProductVariantAttributeId = 6
				) val on mapping.Id = val.ProductVariantAttributeId
		)

		insert into [dbo].[ProductVariantAttributeValue] (ProductVariantAttributeId, Alias, Name, ColorSquaresRgb, PriceAdjustment, WeightAdjustment, IsPreSelected, DisplayOrder, ValueTypeId, LinkedProductId, Quantity)
		select newItems.Id as ProductVariantAttributeId, ExistingItems.Alias, ExistingItems.Name, ExistingItems.ColorSquaresRgb, ExistingItems.PriceAdjustment, ExistingItems.WeightAdjustment, ExistingItems.IsPreSelected, ExistingItems.DisplayOrder, ExistingItems.ValueTypeId, ExistingItems.LinkedProductId, ExistingItems.Quantity from [dbo].[Product_ProductAttribute_Mapping] newItems
		cross join ExistingItems
		where newItems.Id > 15

The trick was to create a CTE to group all the existing items, then cross join that with all new entries in [dbo].[Product_ProductAttribute_Mapping].