Gold R6 orphaned ac_UpsellProducts records

[AbleMods]

This is fixed in later releases, but I'm posting this here for future reference.

In Gold R6, deleting a product that is assigned as an upsell to another product does not remove the associated ac_UpsellProducts record. This leaves the orphaned records to accumulate in the ac_UpsellProducts table. It also causes the Upsell page (rendered after a product with assigned upsells is added to the basket) to throw an exception.

To identify if you have orphaned upsell records, simply run this SQL query against your store database:

Code: Select all

use <dbname>
SELECT u.productid, p.productid, p.name FROM ac_upsellproducts u
      LEFT OUTER JOIN ac_products p ON u.childproductid = p.productid
      WHERE p.productid IS NULL

If any records are returned, you have orphaned upsell records.

To clean them out, simply run this query:

Code: Select all

use <dbname>
DELETE FROM ac_upsellproducts u
      LEFT OUTER JOIN ac_products p ON u.childproductid = p.productid
      WHERE p.productid IS NULL

To fix the issue going forward, you must have full source code. Open full source and modify the /Products/ProductRepository.cs file. Locate this routine:

Code: Select all

public override void BeforeDelete(object entity)

Find this code at the end of the routine:

Code: Select all

            // DELETE CUSTOM URL
            if (item.CustomUrlObject != null)
                item.CustomUrlObject.Delete();

and replace it with this code:

Code: Select all

            // DELETE CUSTOM URL
            if (item.CustomUrlObject != null)
                item.CustomUrlObject.Delete();

            // BEGIN MOD: AbleMods.com
            // DATE:  11/12/2014
            // BUG FIX: when a product is deleted, associated upsell records must also be deleted.  This code copied from Gold R9.
            // REMOVE UPSELL ASSOCIATIONS THEY EXIST
            bool associatedAsUpsell = NHibernateHelper.CreateCriteria<UpsellProduct>()
                .Add(Restrictions.Eq("ChildProductId", item.Id))
                .SetProjection(Projections.RowCount())
                .UniqueResult<int>() > 0;

            if (associatedAsUpsell)
            {
                NHibernateHelper.CreateSQLQuery("DELETE FROM ac_UpsellProducts WHERE ChildProductId = :childProductId")
                    .SetParameter("childProductId", item.Id)
                    .ExecuteUpdate();
            }
            // END MOD: AbleMods.com

Now upsell records will be properly deleted when the upsell product is deleted from the catalog.

Not entirely sure why SQL referential integrity isn't handling this automatically since there is a foreign key constraint with a CASCADE: DELETE dependency. But that's another fight for another day.

Enjoy

[jguengerich]

Not entirely sure why SQL referential integrity isn't handling this automatically since there is a foreign key constraint with a CASCADE: DELETE dependency. But that's another fight for another day.

I think it is because the constraint is on the ProductId field, not the ChildProductId field. So deleting a product from the Products table will delete all the records in the Upsells table of things that were upsells for it. I suppose another fix for the "opposite" situation you describe would be to add a constraint to the ChildProductId field.

[AbleMods]

Good point!

Yea I didn't want to mess with constraints for fear it would break a future Able update script.