Useful Groovy Scripts. Part 4. Finding all external references to an object
Finding all external references to an object (EARLY BETA)
The script I present today is designed for finding the references for any particular item in hybris. It can be useful if you want to figure out if the product or image is used somewhere in the system or it can be safely removed. It is a beta version, and it doesn’t cover everything, so use it at your own risk 🙂 For demonstrating purposes, let’s take the image which is definitely linked to the product. Let’s start with the simplest case,
The script shows all objects (here products) having the particular object (here Media) set as a value of one of the attributes with “Media” type. For 8797134192670 the script shows:
The first item in the list is a product with the PK 8796373942273. All other items are product variants which have the image assigned as well.
There is a special case with the collection of objects. The collection is stored as a comma-separated PKs. In SAP hybris accelerators, the collections are used quite intensively, despite the fact SAP don’t recommend using them in the projects. Let’s take the following example:
We expect that the system will determine the objects this image is used with and one of these is this product.
String pkObj = "8797135929374";The script shows all objects having the references to the object with this PK:
We can see that our expected product is a first item in the list. All other products are different versions of the product.
This media is found in the collection attribute, “thumbnail”. Internally, the collections are stored as comma-separated values, so for our product the value of the attribute looks like this:
,#1,8797135896606,8797135962142,8797135994910, 8797135929374,8797136027678,
8797136060446, 8797136093214,
Let’s take another example:
For the particular media container (8796244312114) the system shows the list of objects which use the media container.
You can see both Media objects on the list because both use the same media container.
Categories
Let’s take the following example:
For PK=8796146172046 (Digital Compacts, the Category object), the script shows the following results:
That means that the category is used in the following external objects:
- Category/Principal relation,
- Category/Product relation,
- Category/Category relation.
There are no products because the Brand category is not linked to products directly.
How it works
The system determines a type of the specified PK. For this type, the system extracts the type hierarchy. For each type in the hierarchy, the system finds the types with the attributes of the particular types or involved in hybris relations. For collections and relations, there is special handling. Finally, the system creates an SQL query and executes it. For example, for “Category” object the SQL will look like:SELECT 'ClassAttributeAssignment','cat2attrrel',"(cat2attrrel.p_classificationclass = '8796146172046' AND TypePkString = 8796110258258)", cat2attrrel.pk
FROM cat2attrrel
WHERE
(cat2attrrel.p_classificationclass = '8796146172046' AND TypePkString = 8796110258258)
UNION
SELECT 'CockpitItemTemplate','cockpittemplclassifrels',"(cockpittemplclassifrels.TargetPK = '8796146172046')", cockpittemplclassifrels.pk
FROM cockpittemplclassifrels
WHERE
(cockpittemplclassifrels.TargetPK = '8796146172046')
UNION
SELECT 'TextFieldConfiguratorSetting','configuratorsettings',"(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)", configuratorsettings.pk
FROM configuratorsettings
WHERE
(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)
UNION
SELECT 'CategoryCatalogVersionDifference','catverdiffs',"(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR (catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)", catverdiffs.pk
FROM catverdiffs
WHERE
(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR
(catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)
UNION
SELECT 'CMSSite','cmssite',"(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)", cmssite.pk
FROM cmssite
WHERE
(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)
UNION
SELECT 'SolrCategoryRedirect','solrabskwrdredir',"(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)", solrabskwrdredir.pk
FROM solrabskwrdredir
WHERE
(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)
UNION
SELECT 'PreviewData','previewdata',"(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)", previewdata.pk
FROM previewdata
WHERE
(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)
UNION
SELECT 'AsCategoryAwareSearchConfiguration','assearchconf',"(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)", assearchconf.pk
FROM assearchconf
WHERE
(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)
UNION
SELECT 'ProductCategoryRestriction','categories',"(restrictions.p_categories LIKE '%8796146172046%')", restrictions.pk
FROM restrictions
WHERE
(restrictions.p_categories LIKE '%8796146172046%')
UNION
SELECT 'Principal','cat2princrel',"(cat2princrel.SourcePK = '8796146172046')", cat2princrel.pk
FROM cat2princrel
WHERE
(cat2princrel.SourcePK = '8796146172046')
UNION
SELECT 'Media','cat2medrel',"(cat2medrel.SourcePK = '8796146172046')", cat2medrel.pk
FROM cat2medrel
WHERE
(cat2medrel.SourcePK = '8796146172046')
UNION
SELECT 'Product','cat2prodrel',"(cat2prodrel.SourcePK = '8796146172046')", cat2prodrel.pk
FROM cat2prodrel
WHERE
(cat2prodrel.SourcePK = '8796146172046')
UNION
SELECT 'Keyword','cat2keywordrel',"(cat2keywordrel.SourcePK = '8796146172046')", cat2keywordrel.pk
FROM cat2keywordrel
WHERE
(cat2keywordrel.SourcePK = '8796146172046')
UNION
SELECT 'Category','cat2catrel',"(cat2catrel.SourcePK = '8796146172046') OR (cat2catrel.TargetPK = '8796146172046')", cat2catrel.pk
FROM cat2catrel
WHERE
(cat2catrel.SourcePK = '8796146172046') OR
(cat2catrel.TargetPK = '8796146172046')
UNION
SELECT 'ProductPromotion','catpromrelations',"(catpromrelations.SourcePK = '8796146172046')", catpromrelations.pk
FROM catpromrelations
WHERE
(catpromrelations.SourcePK = '8796146172046')
UNION
SELECT 'CMSCategoryRestriction','categories4restriction',"(categories4restriction.TargetPK = '8796146172046')", categories4restriction.pk
FROM categories4restriction
WHERE
(categories4restriction.TargetPK = '8796146172046')
UNION
SELECT 'ProductCarouselComponent','catsforprodcarcomprels',"(catsforprodcarcomprels.TargetPK = '8796146172046')", catsforprodcarcomprels.pk
FROM catsforprodcarcomprels
WHERE
(catsforprodcarcomprels.TargetPK = '8796146172046')
FROM cat2attrrel
WHERE
(cat2attrrel.p_classificationclass = '8796146172046' AND TypePkString = 8796110258258)
UNION
SELECT 'CockpitItemTemplate','cockpittemplclassifrels',"(cockpittemplclassifrels.TargetPK = '8796146172046')", cockpittemplclassifrels.pk
FROM cockpittemplclassifrels
WHERE
(cockpittemplclassifrels.TargetPK = '8796146172046')
UNION
SELECT 'TextFieldConfiguratorSetting','configuratorsettings',"(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)", configuratorsettings.pk
FROM configuratorsettings
WHERE
(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)
UNION
SELECT 'CategoryCatalogVersionDifference','catverdiffs',"(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR (catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)", catverdiffs.pk
FROM catverdiffs
WHERE
(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR
(catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)
UNION
SELECT 'CMSSite','cmssite',"(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)", cmssite.pk
FROM cmssite
WHERE
(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)
UNION
SELECT 'SolrCategoryRedirect','solrabskwrdredir',"(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)", solrabskwrdredir.pk
FROM solrabskwrdredir
WHERE
(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)
UNION
SELECT 'PreviewData','previewdata',"(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)", previewdata.pk
FROM previewdata
WHERE
(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)
UNION
SELECT 'AsCategoryAwareSearchConfiguration','assearchconf',"(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)", assearchconf.pk
FROM assearchconf
WHERE
(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)
UNION
SELECT 'ProductCategoryRestriction','categories',"(restrictions.p_categories LIKE '%8796146172046%')", restrictions.pk
FROM restrictions
WHERE
(restrictions.p_categories LIKE '%8796146172046%')
UNION
SELECT 'Principal','cat2princrel',"(cat2princrel.SourcePK = '8796146172046')", cat2princrel.pk
FROM cat2princrel
WHERE
(cat2princrel.SourcePK = '8796146172046')
UNION
SELECT 'Media','cat2medrel',"(cat2medrel.SourcePK = '8796146172046')", cat2medrel.pk
FROM cat2medrel
WHERE
(cat2medrel.SourcePK = '8796146172046')
UNION
SELECT 'Product','cat2prodrel',"(cat2prodrel.SourcePK = '8796146172046')", cat2prodrel.pk
FROM cat2prodrel
WHERE
(cat2prodrel.SourcePK = '8796146172046')
UNION
SELECT 'Keyword','cat2keywordrel',"(cat2keywordrel.SourcePK = '8796146172046')", cat2keywordrel.pk
FROM cat2keywordrel
WHERE
(cat2keywordrel.SourcePK = '8796146172046')
UNION
SELECT 'Category','cat2catrel',"(cat2catrel.SourcePK = '8796146172046') OR (cat2catrel.TargetPK = '8796146172046')", cat2catrel.pk
FROM cat2catrel
WHERE
(cat2catrel.SourcePK = '8796146172046') OR
(cat2catrel.TargetPK = '8796146172046')
UNION
SELECT 'ProductPromotion','catpromrelations',"(catpromrelations.SourcePK = '8796146172046')", catpromrelations.pk
FROM catpromrelations
WHERE
(catpromrelations.SourcePK = '8796146172046')
UNION
SELECT 'CMSCategoryRestriction','categories4restriction',"(categories4restriction.TargetPK = '8796146172046')", categories4restriction.pk
FROM categories4restriction
WHERE
(categories4restriction.TargetPK = '8796146172046')
UNION
SELECT 'ProductCarouselComponent','catsforprodcarcomprels',"(catsforprodcarcomprels.TargetPK = '8796146172046')", catsforprodcarcomprels.pk
FROM catsforprodcarcomprels
WHERE
(catsforprodcarcomprels.TargetPK = '8796146172046')
THE SCRIPT
(github)© Rauf Aliev, December 2017
