Updating Product table via SQL causes product links to not work until Manually saved

Hello,
I am trying to manage my products strictly with SQL queries and have come across an issue.  I recently wanted to group all my Simple Products into a Grouped Product where the Name of the product was the same.

I updated the following 3 tables:
- Product
- Product_Category_Mapping
- Product_Picture_Mapping

EXAMPLE:
New product in [Product] table, Id = 8296.  This is set to a Grouped Product using the ProductTypeId=10 and ProductTemplateId=2

All other products with same name as this group were set the following way:
ParentGroupProductId=8296
VisibleIndividually=0

The Id 8296 was inserted via SQL into the Product_Picture_Mapping and Product_Category_Mapping tables.


When I locate the Grouped Product on my site I see it and it displays the image but the title is not a hyperlink.

TO FIX
If I go into Admin panel and Edit the Grouped Product and just click Save then the Grouped Product is now a hyperlink and works correctly.  


What is the code doing in the Save button that I am not doing with a SQL update?

Thank you for any information.


PS: I have cleared the cache on the site and recycled the web app hoping that there was some initialization procedure that could fix my issue but that didn't work. :(

T-Soft wrote:

Hi,

When you click SAVE, the code behind do something very important - it validates the seName and save its slug:

//search engine name
model.SeName = product.ValidateSeName(model.SeName, product.Name, true);
_urlRecordService.SaveSlug(product, model.SeName, 0);

This is what you miss in your SQL.

Actually doing the whole thing via SQL is not the best approach, because you can duplicate some seNames...

Maybe you should go through the Edit Action of the Admin ProductController and see what is necessary for the saving of product.

I hope that helped you !

thank you!  this surely does help!  :)

ok, manually inserting a record in the UrlRecord table does not make the product link work.  I am determined to find out how to do this via SQL as I have too many Grouped Products to try to do by hand :)  Will post my findings once resolved.

Here's the SQL I use for this.  It might need tweaking to your requirements but it should get you most of the way there.  You might want to put it in a transaction and set it to rollback until your satisfied that it's doing what you expect. This handles the issue of duplicate slugs in a similar way to nop by appending a number on the end of the duplicate URLs.

If you're not seing your manually inserted links as active it might be worth clearing the nop cache from the admin area (though I don't remember this being an issue).

  WITH Slugs AS (
    SELECT Id AS EntityId
        ,'Product' AS EntityName
        ,Name
        ,dbo.FormatUrl(Name) AS Slug
        ,ROW_NUMBER() OVER (PARTITION BY dbo.FormatUrl(Name) ORDER BY id) AS Occur
        ,Published
        ,Deleted
        ,1 AS IsActive
      FROM Product
  )

  MERGE UrlRecord AS mTarget
  USING (
  SELECT EntityId
      ,EntityName
      ,CASE WHEN Occur > 1
         THEN Slug + '-' + CONVERT(varchar(5), Occur)
       ELSE Slug
       END AS Slug
      ,Published
      ,Deleted
      ,IsActive
    FROM Slugs
       ) AS mSource
  ON (mSource.EntityId = mTarget.EntityId
      AND mSource.EntityName = mTarget.EntityName
    AND mSource.Slug = mTarget.Slug)
  WHEN NOT MATCHED BY TARGET AND mSource.Deleted = 0 --New Product or changed name
    THEN INSERT
         (EntityId
         ,EntityName
         ,Slug
         ,IsActive
         ,LanguageId)
     VALUES(EntityId   --EntityId
         ,EntityName --EntityName
         ,Slug       --Slug
         ,IsActive   --IsActive
         ,0          --LanguageId
         )
  WHEN NOT MATCHED BY SOURCE AND mTarget.EntityName = 'Product' AND mTarget.IsActive = 1 --Old name
    THEN UPDATE SET mTarget.IsActive = 0
  WHEN MATCHED AND mTarget.IsActive != mSource.IsActive --Reactivated Product?
    THEN UPDATE SET mTarget.IsActive = mSource.IsActive
  WHEN MATCHED AND mSource.Deleted = 1 --Deleted Product
    THEN DELETE
  OUTPUT $action, inserted.*, deleted.*;

...just remembered this relies on a couple of scalar valued functions for formatting the URLs otherwise it won't work.  These are the functions:

CREATE Function [dbo].[FormatUrl](@Temp nVarChar(1000))
Returns nVarChar(1000)
AS
BEGIN
  SET @Temp = dbo.RemoveNonAlphaCharacters(@Temp)
    SET @Temp = RTRIM(@Temp)
    SET @Temp = REPLACE(@Temp,' ','-')
    SET @Temp = REPLACE(@Temp,'--','-')
    SET @Temp = REPLACE(@Temp,'--','-')
    RETURN @Temp
End


CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp nVarChar(1000))
Returns nVarChar(1000)
AS
BEGIN

    DECLARE @KeepValues as varchar(50)
    SET @KeepValues = '%[^a-zA-Z0-9_ -]%'
    WHILE PatIndex(@KeepValues, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    RETURN @Temp
End

This maintains the same casing as in the product name but just add a TOLOWER in there somewhere if you prefer all lowercase URLs.

...oh and one more thing. This recreates the URL for every product in the database based on its name (but only updates it if it's actually different) which is ok if that's what you want but not so good if you've modified the SEO friendly urls in nop by hand since it'll deactivate the custom url and restore the default url.

Or take nop-templates' very reasonable advice and let the nop do the heavy lifting for you :-)

You are awesome petemitch!  Will definitely comb through that sql to see how you did it. :)

Thought  I would post an update and the real resolution to my problem.  first of all thanks to those who have replied and lead me on the correct coarse, got some cool useful code ;).  

So after inserting a proper UrlRecord the hyperlink for the Product Name was still coming up with nothing.  I walked through the create_sample_data.sql that loads our stores with default products and followed the insertion of a grouped product.  NOTHING was different I could see so I dumped all the records of the tables that sql touched related to a working and non-working grouped product I had.  I found nothing different...grrrr....

I saved off the dump to a txt file and went into the administration panel and edited the non-working grouped product and just clicked saved.  I then reran the dump of the sql tables and saved it off.

when I compared the 2 files for changes I couldn't believe my eyes!  The only thing that changed was the ShortDescription, FullDescription and AdminComment fields were set to NULL rather than my initial ''.  

I tested this with another non-working grouped product and just set those fields to NULL and everything worked!

Wow, that was painful lol.  

tldr;
Product fields [ShortDecription], [FullDescription] and [AdminComment] should be set to NULL when not being used for Grouped Product types!

Whew, I almost beat my brains out on my keyboard on this one! lol

You can try something like this which worked for me in 3.8. Please test before reusing first!

for (var i = 1; i < 1000; i++) // productids
            {
                var tProduct = _productService.GetProductById(i);
                var seName = tProduct.GetSeName(1, false, false);
                var validatedSeName = tProduct.ValidateSeName(seName, tProduct.Name, true);
                _urlRecordService.SaveSlug(tProduct, validatedSeName, 1);
            }