Gold R6 orphaned ac_UpsellProducts records
Posted: Wed Nov 12, 2014 6:02 am
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:
If any records are returned, you have orphaned upsell records.
To clean them out, simply run this query:
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:
Find this code at the end of the routine:
and replace it with this code:
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
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
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
Code: Select all
public override void BeforeDelete(object entity)
Code: Select all
// DELETE CUSTOM URL
if (item.CustomUrlObject != null)
item.CustomUrlObject.Delete();
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
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