Entity Mapping Product_Category_Mapping with many to many

Hi all,

I have created three new feilds in the Product_Category_Mapping

CCCAnalCode

CCCCategoryDescription

CCCStockcode.

The Fields I have highlighted in code have also been generated in the Product table I need these to be mapped to one another as well as the ProductId and CategoryId.

this.ToTable("Product_Category_Mapping");
            this.HasKey(pc => pc.Id);
            
            this.HasRequired(pc => pc.Category)
                .WithMany(c => c.ProductCategories)
                .HasForeignKey(pc => pc.CategoryId);


            this.HasRequired(pc => pc.Product)
                .WithMany(p => p.ProductCategories)
                .HasForeignKey(pc => pc.ProductId);

Within the the Csharp file I see a foreign key has been generated to connect with a many to many relationship of there Id, as a novice to connect the fields I have generated I'm hoping someone could advise me on whether I should mirror this process.

For example

this.HasRequired(pc =>pc.Category)
     .WithMany(c => c.ProductCategories)
     .HasForeignKey(pc =>pc.CCCAnalCode)

this.HasRequired(pc =>pc.Product)
     .WithMany(c => c.ProductCategories)
     .HasForeignKey(pc =>pc.CCCAnalCode)

Hope this makes sense.

All help highly appreciated and kind regards

Richard.

Hi Richard,

Can you explain why you are storing the same data in the product table and the product_category_mapping table?

I'm no expert but it is my understanding that you should either store it in the product table if the data is product specific or in the mapping table if the data is mapping specific.

If both cases are true i.e. the product has base data but data maybe different on each mapping then there is no reason to set keys up.

HTH

Dave

Okay I see your point and I spoke with my boss and we agreed on stockcode as there is no point in holding to feilds with the same data.

But for further info the in house system here works on Anal Codes instead of Categories which we need to map as we will be replicating from one db to the other.

So for example baby care is BC01 so the category is Baby Care in Nop db, in our in house db its BB01.

Using the Product Id and Category Id as foreign keys we think it could be a good idea to also use the Anal Codes as a foreign key as we are replicating on a daily basis through both systems. With continues product uploads.

So a product in your houseDB has one Anal Code which indicates it's category?

If so this is how I would do it;-

When you import a product to NOP from your in house DB you will need a lookup table (Anal_Category_Mapping) which has the Analcode - Nop CategoryId for each  mapping in it.

You lookup the anal code in Anal_Category_Mapping and get the Nop CategoryId and create the Product Category Mapping.

When you export you look up the nop CategoryId in Anal_Category_Mapping and get the anal code.

This way you are not touching the Product, Category or ProductCategory entities.

AFAIK accessing those entities/tables using a keyed int id field is going to be fastest.

HTH

Dave

Yes so lets say BC00 has 300 products it has subcategories BC01 BC02 etc In Nop they are displayed with the correct Category name.

Novice here bit confused understand creating tables etc but would I be adding a new table completely to the solution or a system table,

You lookup the anal code in Anal_Category_Mapping and get the Nop CategoryId and create the Product Category Mapping.

When you export you look up the nop CategoryId in Anal_Category_Mapping and get the anal code.


Would I create a new table with the primary key as Anal Code with a foreign key to the Category Id. This then would map to the fk in the product_cataegry_mapping...

I have looked in the stored procedure and I see that there are left outer joins that map productcategory table..Is

INSERT INTO #DisplayOrderTmp ([ProductId])
  SELECT p.Id
  FROM Product p with (NOLOCK)
  LEFT OUTER JOIN Product_Category_Mapping pcm with (NOLOCK) ON p.Id=pcm.ProductId
Add

        LEFT OUTER Join Anal_Category_Mapping pcm with (NOLOCK) ON p.Id=pcm.ProductId

Hi Richard,

I'm assuming you are going to be creating products / categories / mappings within Nop by drawing data from your existing system?

I'd advise this anyway rather .

So your first step is to retrieve a product from your existing system somehow? at this stage, currently, you only know the Analcode.

It's up to you where you put the Anal_Category_Mapping table you could put it in your in house db and instead of returning the analcode to Nop return the correct Nop CategoryId.

Alternatively create the Anal_Category_Mapping table in Nop and lookup the Anal code there.

I'd go with what your familiar with.

I think there are some guides on how to add new tables to Nop on the forum however and as this is not a big job, it might be an idea to follow one through to give you more of a handle on nop?

Sorry I'm not going to be a lot of further help as I think you're in nop 2+? which I'm just getting to grips with myself.

Hi Dave

Yes I'm In 2.3, I can create a table and draw it through the entity I have extended the solution by adding 12 new feilds to the product table that are displaying on the frontend through the nop db.. We are starting full testing next week.

This table though that we are discussing would remain in the core and data thinking of the top of my head.. As we won't need the Anal Code displaying just drawing the data to the correct product. That's why I thought I would add the Anal code to the product_category_Mapping alongside the product table and create a foriegn key/inner join so it connects it aswell as the id's. But also connects to the anal code stored in the stock table in the existing db.

Try and find out is the best option I think..

Cooool

Richard

Hi Daveb

Been along time since I have had to re-adress this. Knowing that I have more experience with Nop it self and this companies inhouse db. I was hoping you would be able to help me on this small part of the project......??

To insert products from one db to the other we are using the Product SKU/Stockcode I have updated all the nessercay Classes to insert using the SKU. I have devised a 1-4 rule to insert a single product form one db to the other. I can manage to trigger an Insert Update and delete with a multiple product's. This is the insert trigger. I have highlighted in bold where I'm having problems on this script.

USE [cccsql]
GO
/****** Object:  Trigger [dbo].[tr_insert_product_details_Stock]    Script Date: 11/28/2012 12:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_insert_product_details_Stock]
ON [dbo].[STOCK]
AFTER INSERT  
AS

DECLARE @newDESCRIPTION varchar(36)
DECLARE @newPACK smallint       
DECLARE @newLANE varchar(2)  
DECLARE @newWHOLESALE_PRICE money
DECLARE @newRETAIL_PRICE money  
DECLARE @newMRP money
DECLARE @newQTYINSTOCK float
DECLARE @newBAY varchar(3)
DECLARE @newSOURCEBARCODE varchar(13)
DECLARE @newRETAILBARCODE varchar(13)
DECLARE @newWEIGHT float
DECLARE @newQTYPACKSPERLAYER smallint
DECLARE @newQTYLAYERSPERPALLET smallint
DECLARE @newPACKTOTALPERPALLET smallint
DECLARE @newANALCODE varchar(8)
DECLARE @newSTOCKCODE varchar(9)
DECLARE @productId int
DECLARE @categoryId int

SET @newSTOCKCODE = (SELECT [STOCK CODE] FROM Inserted)
SET @newDESCRIPTION = (SELECT [DESCRIPTION] FROM Inserted)
SET @newPACK = (SELECT PACK FROM Inserted)
SET @newLANE = (SELECT LANE FROM Inserted)
SET @newWHOLESALE_PRICE = (SELECT [WHOLESALE PRICE] FROM Inserted)
SET @newRETAIL_PRICE = (SELECT [RETAIL PRICE] FROM Inserted)
SET @newMRP = (SELECT MRP FROM Inserted)
SET @newQTYINSTOCK = (SELECT [QTY IN STOCK] FROM Inserted)
SET @newBAY = (SELECT [BAY] FROM Inserted)
SET @newSOURCEBARCODE = (SELECT [SOURCE BAR CODE] FROM Inserted)
SET @newRETAILBARCODE = (SELECT [RETAIL BAR CODE] FROM Inserted)
SET @newWEIGHT = (SELECT [WEIGHT] FROM Inserted)
SET @newQTYPACKSPERLAYER = (SELECT [QTY PACKS PER LAYER] FROM Inserted)
SET @newQTYLAYERSPERPALLET = (SELECT [QTY LAYERS PER PALLET] FROM Inserted)
SET @newPACKTOTALPERPALLET = (SELECT [PACK TOTAL PER PALLET] FROM Inserted)
SET @newANALCODE = (SELECT [ANAL CODE] FROM Inserted)
       
IF TRIGGER_NESTLEVEL() > 1
RETURN     


INSERT INTO [ConcordWholesales_new].[dbo].[Product]
           ([Name]
           ,[ShortDescription]
           ,[FullDescription]
           ,[AdminComment]
           ,[ProductTemplateId]
           ,[ShowOnHomePage]
           ,[MetaKeywords]
           ,[MetaDescription]
           ,[MetaTitle]
           ,[SeName]
           ,[AllowCustomerReviews]
           ,[ApprovedRatingSum]
           ,[NotApprovedRatingSum]
           ,[ApprovedTotalReviews]
           ,[NotApprovedTotalReviews]
           ,[Published]
           ,[Deleted]
           ,[CreatedOnUtc]
           ,[UpdatedOnUtc]
           ,[SKU]
           ,[CCCPack]
           ,[CCCLane]
           ,[CCCWholesalePrice]
           ,[CCCRetailPrice]
           ,[CCCMRP]
           ,[CCCQTYINSTOCK]
           ,[CCCBay]
           ,[CCCSourceBarcode]
           ,[CCCRetailBarcode]
           ,[CCCWeight]
           ,[CCCQTYPacksPerLayer]
           ,[CCCQTYLayersPerPallet]
           ,[CCCQTYTotalPerPallet]
           ,[CCCAnalCode])
     VALUES
           ( @newDESCRIPTION,
             NULL,
             NULL,
             NULL,
             '3',
             '0',
             NULL,
             NULL,
             NULL,
             NULL,
             '1',
             '0',
             '0',
             '0',
             '0',
             '1',
             '0',
            GETUTCDATE(),
            GETUTCDATE(),
            @newSTOCKCODE,
            @newPACK,
            @newLANE,
            @newWHOLESALE_PRICE,
            @newRETAIL_PRICE,
            @newMRP,
            @newQTYINSTOCK,
            @newBAY,
            @newSOURCEBARCODE,
            @newRETAILBARCODE,
            @newWEIGHT,
            @newQTYPACKSPERLAYER,
            @newQTYLAYERSPERPALLET,
            @newPACKTOTALPERPALLET,
            @newANALCODE)


SELECT      @productId =  SCOPE_IDENTITY()
SELECT      @categoryId = @@IDENTITY
            
INSERT INTO [ConcordWholesales_new].[dbo].[Product_Category_Mapping]
           ([ProductId]
           ,[CategoryId]
           ,[IsFeaturedProduct]
           ,[DisplayOrder]
           ,[CCCAnalCode]
           ,[CCCSKU])
VALUES
           (@productId
           ,@categoryId
           ,'False'
           ,'1'
           ,@newANALCODE
           ,@newSTOCKCODE)

SELECT      @productId = SCOPE_IDENTITY()
            
INSERT INTO [ConcordWholesales_new].[dbo].[ProductVariant]
           ([ProductId]
           ,[Name]
           ,[Sku]
           ,[Description]
           ,[AdminComment]
           ,[ManufacturerPartNumber]
           ,[Gtin]
           ,[IsGiftCard]
           ,[GiftCardTypeId]
           ,[RequireOtherProducts]
           ,[RequiredProductVariantIds]
           ,[AutomaticallyAddRequiredProductVariants]
           ,[IsDownload]
           ,[DownloadId]
           ,[UnlimitedDownloads]
           ,[MaxNumberOfDownloads]
           ,[DownloadExpirationDays]
           ,[DownloadActivationTypeId]
           ,[HasSampleDownload]
           ,[SampleDownloadId]
           ,[HasUserAgreement]
           ,[UserAgreementText]
           ,[IsRecurring]
           ,[RecurringCycleLength]
           ,[RecurringCyclePeriodId]
           ,[RecurringTotalCycles]
           ,[IsShipEnabled]
           ,[IsFreeShipping]
           ,[AdditionalShippingCharge]
           ,[IsTaxExempt]
           ,[TaxCategoryId]
           ,[ManageInventoryMethodId]
           ,[StockQuantity]
           ,[DisplayStockAvailability]
           ,[DisplayStockQuantity]
           ,[MinStockQuantity]
           ,[LowStockActivityId]
           ,[NotifyAdminForQuantityBelow]
           ,[BackorderModeId]
           ,[AllowBackInStockSubscriptions]
           ,[OrderMinimumQuantity]
           ,[OrderMaximumQuantity]
           ,[DisableBuyButton]
           ,[DisableWishlistButton]
           ,[CallForPrice]
           ,[Price]
           ,[OldPrice]
           ,[ProductCost]
           ,[SpecialPrice]
           ,[SpecialPriceStartDateTimeUtc]
           ,[SpecialPriceEndDateTimeUtc]
           ,[CustomerEntersPrice]
           ,[MinimumCustomerEnteredPrice]
           ,[MaximumCustomerEnteredPrice]
           ,[Weight]
           ,[Length]
           ,[Width]
           ,[Height]
           ,[PictureId]
           ,[AvailableStartDateTimeUtc]
           ,[AvailableEndDateTimeUtc]
           ,[Published]
           ,[Deleted]
           ,[DisplayOrder]
           ,[CreatedOnUtc]
           ,[UpdatedOnUtc]
           ,[CCCAnalCode])
VALUES
           (@productId
           ,@newDESCRIPTION
           ,@newSTOCKCODE
           ,Null
           ,Null
           ,@newRETAILBARCODE
           ,@newSOURCEBARCODE
           ,'False'
           ,'0'
           ,'False'
           ,Null
           ,'False'
           ,'False'
           ,'0'
           ,'False'
           ,'0'
           ,Null
           ,'0'
           ,'False'
           ,'0'
           ,'False'
           ,Null
           ,'False'
           ,'0'
           ,'0'
           ,'0'
           ,'True'
           ,'True'
           ,'0'
           ,'False'
           ,'1'
           ,'1'
           ,@newQTYINSTOCK 
           ,'True'          
           ,'True'
           ,'6'
           ,'6'
           ,'2'
           ,'0'
           ,'False'
           ,'1'
           ,'10000'
           ,'False'
           ,'True'
           ,'False'
           ,@newWHOLESALE_PRICE
           ,@newRETAIL_PRICE
           ,'0'
           ,NULL
           ,NULL
           ,NULL
           ,'False'
           ,'0.0'
           ,'0.0'
           ,@newWEIGHT
           ,isnull(@newQTYPACKSPERLAYER,'')
           ,isnull(@newQTYLAYERSPERPALLET,'')
           ,isnull(@newPACKTOTALPERPALLET,'')
           ,'0'
           ,null
           ,null
           ,'True'
           ,'False'
           ,'1'
           ,GETUTCDATE()
           ,GETUTCDATE()
           ,isnull(@newANALCODE,''))

The CategoryId is returning the following error

The INSERT statement conflicted with the FOREIGN KEY constraint "ProductCategory_Category". The conflict occurred in database "ConcordWholesales_new", table "dbo.Category", column 'Id'.

Part of the update trigger is as follow's but it keeps returning a null value or constraint error with the column Id.

--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CategoryId in (select Id from ConcordWholesales_new.dbo.Category where @categoryId = CategoryId)
--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CCCSKU=@newSTOCKCODE
--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CCCSKU=@newSTOCKCODE

Hopefully you maybe able to show me where the problem is above.

The Anal Codes have a datatype of Varchar Im aware they can't be converted into Int in Sql 2008 R2. I have started to write a pluggin but Dave I was hoping there could be a quicker way of do this.

If you need me to clarify and explain further they I will humbly appreciate all help

If you could help BTW it would be humbly appreicated.

Richard

Not really done triggers but this line

SELECT      @categoryId = @@IDENTITY

Looks to be incorrect. Thats the identity of the product you just entered not a category Id?

additionally I'm not entirely sure what you are trying to do with these lines

--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CategoryId in (select Id from ConcordWholesales_new.dbo.Category where @categoryId = CategoryId)

--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CCCSKU=@newSTOCKCODE

--Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CCCSKU=@newSTOCKCODE

Line one looks to be setting the categoryid to the categoryid where the categoryid = categoryid ????

then why have line 2 repeated in line 3 ????

HTH

Hi Dave

Typo with line 2 and line 3. Sorry for that.

I understand your point with the CategoryId being Identified with the product Id - Rookie mistake.

What I'm trying to acheive is a numerical conversion from a varchar datatype into Int value in the Product_Category_Mapping.

With the AnalCode they have a department code BB that would be used with the ParentCategoryId the Anal Code would be Sub Category BB01.

We are pulling our stock list using the Product Sku from our STOCK table and we are able to populate dbo.Product, dbo.ProductVariant with the triggers but I'm having slight problem with the Product_Category_Mapping. In our STOCK table each product line has an AnalCode as I mention so from the rookie mistake above here is is a revised version of the syntax (One of many) I'm testing...

Update  ConcordWholesales_new.dbo.Product_Category_Mapping set CategoryId=@categoryId where CCCAnalCode=@newANALCODE

If I update Nop with a new feild called Anal Code all tables could pull the Categories through the Anal Codes and Then the Stock through the Sku Code and connect then in the Product_Category_Mapping.

If this makes further sense.

Richard