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. 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. 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. The script extracts all fields from the Category object, filter out the relations (if specified) and maps (always) and resolves the Composed types. 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. 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. 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  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: 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): The list of these products: 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: The don’tGoIntoRelations is for the query above. With  you will have more joins the request will work much slower. CMS Pages: The generated query (with DontGoIntoRelations = false, so you can see content slots here): The output (moved to Excel & transposed) CMS Components The generated FlexibleSearch query: The output:

THE SCRIPT

(github)

© Rauf Aliev, December 2017