Useful Groovy Scripts. Part 3. Flexible Search Query Generator

Today I present a groovy script for creating a Flexible Search query for any hybris type. You can use this query as a template for troubleshooting or data reconciliation. It automatically resolves the RelationType and ComposedType attributes to make the result set human readable.

2017-12-09_12h32_47

It can’t work with collections and maps because FlexibleSearch engine is not designed to work with them too. However, with the attributes of RelationType and Composed types, which make up the majority, it works rather well.

Let’s take the Category type as an example. The first line of our script is our parameter, the hybris item type. There is the only mandatory input, and that is a type name:

TYPE = "Category"

There are some extra optional parameters for whitelisting and blacklisting the particular attributes and types:

dontGoIntoTypes = ["Media"]; //Blacklist types
dontGoIntoRelations = true; // Blacklist all relations except whitelisted
dontGoIntoTheRelations = []; // Blacklist particular relation types
dontGoIntoAttributes = ["itemtype", "owner"]; //blacklist attributes
GoIntoTheRelationTypeAttributes = []; //whitelist particulare relations

After executing the script via HAC, the script shows the following FlexibleSearch query. This query is generated from scratch. It can be used as a template for your own. For example, you can add WHERE clauses to filter the results.

2017-12-08_22h55_08

If you copy this query to HAC and execute it there, you will get an HTML table which can be easily copied to Excel for the analysis.

2017-12-08_22h49_35

The script extracts all fields from the Category object, filter out the relations (if specified) and maps (always) and resolves the Composed types.

2017-12-08_23h00_50

What is “resolved” means? Let’s see the list of attributes of Category. One of the attributes is catalogVersion. The script check the reference types and process their unique attributes. If these attributes are of composed type, it goes further.

fsresolver1

In the report, we see the Catalog.catalogId and CatalogVersion.version because these fields contain the text information. If Catalog had any unique attributes pointing to the 4th object, the system would go there and pull the unique attributes from there.

If the object doesn’t have the unique attributes, the system can’t assume which of the attributes should be substituted in the flexibleSearch query and leave things untouched. For example, for the Product, we have an attribute productOrderLimit of ProductOrderLimit type which has no unique attributes. So the system decides to show PK instead of ProductOrderLimit.code which is not unique.

SELECT 
 {Product.creationtime} as Product_creationtime,
 {Product.modifiedtime} as Product_modifiedtime,
 {Product.pk} as Product_pk, 
 /* ... */
 {ProductOrderLimit.pk} as ProductOrderLimit_pk,
 /* ... */
FROM
 {Product}

There are some limitations, though. Cyclic references are not processed properly. The relations for the sub-objects are not processed.  Maptypes are not processed anywhere. Too many JOINs may slow down the system significantly. I hope some of these things are to be fixed in the later versions.

For example, for out-of-the-box Product type, the system generates the following FlexibleQuery:

SELECT 
 {Product.creationtime} as Product_creationtime,
 {Product.modifiedtime} as Product_modifiedtime,
 {Product.pk} as Product_pk,
 {Product.Europe1PriceFactory_PDG} as Product_Europe1PriceFactory_PDG,
 {Product.Europe1PriceFactory_PPG} as Product_Europe1PriceFactory_PPG,
 {Product.Europe1PriceFactory_PTG} as Product_Europe1PriceFactory_PTG,
 {Product.approvalStatus} as Product_approvalStatus,
 {Product.articleStatus} as Product_articleStatus,
 {Product.buyerIDS} as Product_buyerIDS,
 {Product_catalogVersion_catalog_Join.id} as Product_catalogVersion_catalog_id,
 {Product_catalogVersion_Join.version} as Product_catalogVersion_version,
 {Product.code} as Product_code,
 {Product_contentUnit_Join.code} as Product_contentUnit_code,
 {Product.data_sheet} as Product_data_sheet,
 {Product.deliveryTime} as Product_deliveryTime,
 {Product.description} as Product_description,
 {Product.detail} as Product_detail,
 {Product.ean} as Product_ean,
 {Product.endLineNumber} as Product_endLineNumber,
 {Product.erpGroupBuyer} as Product_erpGroupBuyer,
 {Product.erpGroupSupplier} as Product_erpGroupSupplier,
 {Product.galleryImages} as Product_galleryImages,
 {Product.logo} as Product_logo,
 {Product.manufacturerAID} as Product_manufacturerAID,
 {Product.manufacturerName} as Product_manufacturerName,
 {Product.manufacturerTypeDescription} as Product_manufacturerTypeDescription,
 {Product.maxOrderQuantity} as Product_maxOrderQuantity,
 {Product.minOrderQuantity} as Product_minOrderQuantity,
 {Product.name} as Product_name,
 {Product.normal} as Product_normal,
 {Product.numberContentUnits} as Product_numberContentUnits,
 {Product.offlineDate} as Product_offlineDate,
 {Product.onlineDate} as Product_onlineDate,
 {Product.order} as Product_order,
 {Product.orderQuantityInterval} as Product_orderQuantityInterval,
 {Product.others} as Product_others,
 {Product.priceQuantity} as Product_priceQuantity,
 {Product.remarks} as Product_remarks,
 {Product.segment} as Product_segment,
 {Product.sequenceId} as Product_sequenceId,
 {Product.specialTreatmentClasses} as Product_specialTreatmentClasses,
 {Product.startLineNumber} as Product_startLineNumber,
 {Product.summary} as Product_summary,
 {Product.supplierAlternativeAID} as Product_supplierAlternativeAID,
 {Product.thumbnails} as Product_thumbnails,
 {Product_unit_Join.code} as Product_unit_code,
 {Product_variantType_Join.code} as Product_variantType_code,
 {Product.xmlcontent} as Product_xmlcontent

FROM
 {Product
 LEFT JOIN CatalogVersion as Product_catalogVersion_Join
   ON {Product.catalogVersion} = {Product_catalogVersion_Join.pk}
 LEFT JOIN Catalog as Product_catalogVersion_catalog_Join
   ON {Product_catalogVersion_catalog_Join.pk} = {Product_catalogVersion_Join.catalog}
 LEFT JOIN Unit as Product_contentUnit_Join
   ON {Product.contentUnit} = {Product_contentUnit_Join.pk}
 LEFT JOIN Unit as Product_unit_Join
   ON {Product.unit} = {Product_unit_Join.pk}
 LEFT JOIN VariantType as Product_variantType_Join
   ON {Product.variantType} = {Product_variantType_Join.pk}}

If we execute it in HAC, we’ll get an HTML table. Let’s move it to Excel, transpose it for better presentation. Let’s look closer at the output. You can see that some fields still contain 13-digit PKs. They are collections or enums, and FlexibleSearch doesn’t support both.

2017-12-09_10h21_00.png

Product_unit_code is resolved here via JOIN Unit, so you can see a string value in the table rather than an external key. The same is with catalogVersion – one external reference was resolved into two columns.

For example, we want to display keywords for the Product. It is 1:N relation, one product can have more than one keyword. With dontGoIntoRelation=false our system will generate the longer FlexibleSearch query. Below is a fragment:

SELECT 
 {Product.pk},
 {Product.code},
/* ... */
 {Product_keywords_RelJoin.source} as Product_keywords_RelJoin_source,
 {Product_keywords_catalogVersion_Join_catalog_Join.id} as Product_keywords_catalogVersion_catalog_id,
 {Product_keywords_catalogVersion_Join.version} as Product_keywords_catalogVersion_version,
 {Product_rel_Keyword_keywords_Join.keyword} as Product_rel_Keyword_keywords_keyword,
 {Product_keywords_language_Join.isocode} as Product_keywords_language_isocode
/* ... */
FROM
 {Product
/* ... */
 LEFT JOIN Product2KeywordRelation as Product_keywords_RelJoin
   ON {Product.pk} = {Product_keywords_RelJoin.target}
 LEFT JOIN Keyword as Product_rel_Keyword_keywords_Join
   ON {Product_keywords_RelJoin.source} = {Product_rel_Keyword_keywords_Join.pk}
 LEFT JOIN CatalogVersion as Product_keywords_catalogVersion_Join
   ON {Product_keywords_catalogVersion_Join.pk} = {Product_rel_Keyword_keywords_Join.catalogVersion}
 LEFT JOIN Catalog as Product_keywords_catalogVersion_Join_catalog_Join
   ON {Product_keywords_catalogVersion_Join.catalog} = {Product_keywords_catalogVersion_Join_catalog_Join.pk}
 LEFT JOIN Language as Product_keywords_language_Join
   ON {Product_keywords_language_Join.pk} = {Product_rel_Keyword_keywords_Join.language}
/* ... */
 }

So the system identified that the product-keyword is a relation, and it connects Product with the Keyword table, and Keyword has two unique fields, catalogVersion and catalog, and it joins them as well. Another unique field of Keyword is language, and it is also a composed type, so the system joined the Language as well.

This FlexibleSearch query shows the following results:

2017-12-09_11h44_06

So the relations can add a lot to your results, as well as to performance. So by default they are turned off. You can also explicitly state what relations are desired via the following configuration:

dontGoIntoRelations = true; // Blacklist all relations except whitelisted
GoIntoTheRelationTypeAttributes = [“Product.supercategories“]; //whitelist particulare relations

With these settings, the resulting FlexibleSearch will be shorter.

The transposed fragment of the output (only the first item is shown here; the number of attributes is shortened for the sake of clearity):

2017-12-09_12h17_57

The list of these products:

2017-12-09_12h22_05

Last four columns were joined. Below is a full FlexibleSearch request generated:

SELECT 
 {Product.creationtime} as Product_creationtime,
 {Product.modifiedtime} as Product_modifiedtime,
 {Product.pk} as Product_pk,
 {Product.Europe1PriceFactory_PDG} as Product_Europe1PriceFactory_PDG,
 {Product.Europe1PriceFactory_PPG} as Product_Europe1PriceFactory_PPG,
 {Product.Europe1PriceFactory_PTG} as Product_Europe1PriceFactory_PTG,
 {Product.approvalStatus} as Product_approvalStatus,
 {Product.articleStatus} as Product_articleStatus,
 {Product.buyerIDS} as Product_buyerIDS,
 {Product_catalogVersion_catalog_Join.id} as Product_catalogVersion_catalog_id,
 {Product_catalogVersion_Join.version} as Product_catalogVersion_version,
 {Product.code} as Product_code,
 {Product_contentUnit_Join.code} as Product_contentUnit_code,
 {Product.data_sheet} as Product_data_sheet,
 {Product.deliveryTime} as Product_deliveryTime,
 {Product.description} as Product_description,
 {Product.detail} as Product_detail,
 {Product.ean} as Product_ean,
 {Product.endLineNumber} as Product_endLineNumber,
 {Product.erpGroupBuyer} as Product_erpGroupBuyer,
 {Product.erpGroupSupplier} as Product_erpGroupSupplier,
 {Product.galleryImages} as Product_galleryImages,
 {Product.logo} as Product_logo,
 {Product.manufacturerAID} as Product_manufacturerAID,
 {Product.manufacturerName} as Product_manufacturerName,
 {Product.manufacturerTypeDescription} as Product_manufacturerTypeDescription,
 {Product.maxOrderQuantity} as Product_maxOrderQuantity,
 {Product.minOrderQuantity} as Product_minOrderQuantity,
 {Product.name} as Product_name,
 {Product.normal} as Product_normal,
 {Product.numberContentUnits} as Product_numberContentUnits,
 {Product.offlineDate} as Product_offlineDate,
 {Product.onlineDate} as Product_onlineDate,
 {Product.order} as Product_order,
 {Product.orderQuantityInterval} as Product_orderQuantityInterval,
 {Product.others} as Product_others,
 {Product.priceQuantity} as Product_priceQuantity,
 {Product.remarks} as Product_remarks,
 {Product.segment} as Product_segment,
 {Product.sequenceId} as Product_sequenceId,
 {Product.specialTreatmentClasses} as Product_specialTreatmentClasses,
 {Product.startLineNumber} as Product_startLineNumber,
 {Product.summary} as Product_summary,
 {Product_supercategories_RelJoin.source} as Product_supercategories_RelJoin_source,
 {Product_supercategories_catalogVersion_Join_catalog_Join.id} as Product_supercategories_catalogVersion_catalog_id,
 {Product_supercategories_catalogVersion_Join.version} as Product_supercategories_catalogVersion_version,
 {Product_rel_Category_supercategories_Join.code} as Product_rel_Category_supercategories_code,
 {Product.supplierAlternativeAID} as Product_supplierAlternativeAID,
 {Product.thumbnails} as Product_thumbnails,
 {Product_unit_Join.code} as Product_unit_code,
 {Product_variantType_Join.code} as Product_variantType_code,
 {Product.xmlcontent} as Product_xmlcontent

FROM
 {Product
 LEFT JOIN CatalogVersion as Product_catalogVersion_Join
   ON {Product.catalogVersion} = {Product_catalogVersion_Join.pk}
 LEFT JOIN Catalog as Product_catalogVersion_catalog_Join
   ON {Product_catalogVersion_catalog_Join.pk} = {Product_catalogVersion_Join.catalog}
 LEFT JOIN Unit as Product_contentUnit_Join
   ON {Product.contentUnit} = {Product_contentUnit_Join.pk}
 LEFT JOIN CategoryProductRelation as Product_supercategories_RelJoin
   ON {Product.pk} = {Product_supercategories_RelJoin.target}
 LEFT JOIN Category as Product_rel_Category_supercategories_Join
   ON {Product_supercategories_RelJoin.source} = {Product_rel_Category_supercategories_Join.pk}
 LEFT JOIN CatalogVersion as Product_supercategories_catalogVersion_Join
   ON {Product_supercategories_catalogVersion_Join.pk} = {Product_rel_Category_supercategories_Join.catalogVersion}
 LEFT JOIN Catalog as Product_supercategories_catalogVersion_Join_catalog_Join
   ON {Product_supercategories_catalogVersion_Join.catalog} = {Product_supercategories_catalogVersion_Join_catalog_Join.pk}
 LEFT JOIN Unit as Product_unit_Join
   ON {Product.unit} = {Product_unit_Join.pk}
 LEFT JOIN VariantType as Product_variantType_Join
   ON {Product.variantType} = {Product_variantType_Join.pk}}

 

Other examples:

Media (I added the last line manually to get better picture of the results)

The system generates the following FlexibleSearch query:

2017-12-09_17h41_23

The don’tGoIntoRelations is true for the query above. With false you will have more joins the request will work much slower.

2017-12-09_17h39_49

2017-12-09_17h40_14

CMS Pages:

The generated query (with DontGoIntoRelations = false, so you can see content slots here):

2017-12-09_18h16_01.png

The output (moved to Excel & transposed)

2017-12-09_18h14_44

CMS Components

The generated FlexibleSearch query:

2017-12-09_18h28_24.png

The output:

2017-12-09_18h26_49.png

THE SCRIPT

(github)

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: