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

It can’t work with collections and maps because the FlexibleSearch engine is not designed to work with them either. However, it works rather well with RelationType and composed-type attributes, which make up the majority.

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

TYPE = "Category"

There are some extra optional parameters for whitelisting and blacklisting 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 particular 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 that can be easily copied to Excel for analysis.

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

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

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

If the object doesn’t have unique attributes, the system can’t assume which attributes should be substituted in the FlexibleSearch query and leaves things untouched. For example, for Product, we have an attribute productOrderLimit of ProductOrderLimit type, which has no unique attributes. So the system decides to show the 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. Map types are not processed anywhere. Too many JOINs may slow down the system significantly. I hope some of these things will be fixed in later versions.

For example, for the 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 and transpose it for better presentation. Let’s look more closely at the output. You can see that some fields still contain 13-digit PKs. They are collections or enums, and FlexibleSearch doesn’t support either.

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 applies to catalogVersion: one external reference was resolved into two columns.

For example, we want to display keywords for Product. It is a 1:N relation: one product can have more than one keyword. With

dontGoIntoRelation=false

our system will generate a 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 identifies that the product-keyword is a relation, and it connects Product with the Keyword table. 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 joins Language as well.

This FlexibleSearch query shows the following results:

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

dontGoIntoRelations = true; // Blacklist all relations except whitelisted
GoIntoTheRelationTypeAttributes = ["Product.supercategories"]; // Whitelist particular 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 clarity):

The list of these products:

The 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 a better picture of the results)

The system generates the following FlexibleSearch query:

The dontGoIntoRelations value is

true

for the query above. With

false

you will have more joins, and 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 and transposed):

CMS Components

The generated FlexibleSearch query:

The output:

THE SCRIPT

(github)

© Rauf Aliev, December 2017