Reading collection type data using Flexible search

Question

How to view the list of data stored in Collection type in Hybris using Flexible search?

I have read somewhere that it is stored as PKs so is it possible to parse it somehow?

Answer 1

"CollectionTypes are based on the Java Collection class."

The maximum length of CollectionType database field is limited, so there may be a chance of getting its values truncated due to many records. In addition, the values of CollectionTypes are written in a CSV format and not in a normalized way. Hybris recommends using RelationTypes over CollectionType whenever possible.

How Collection Stores Values :

1. If CollectionType contains AtomicTypes, the values are stored as binary fields in the database.
2. If CollectionType stores collection of items, then items' Primary Keys (PKs) are stored in the database in string form (list of PKs).

Advantage :

1. As all the values of one CollectionType instance are stored as one single field, reading in and writing the values is quite fast as it is done in a single database access (especially with caching).

Disadvantage :

1. If a collection contains a lot of PKs, the field value may reach the maximum length of field for the database implementation and entries may get truncated.

2. As the database entry only contains the PKs, not the items, you cannot run database searches on the entries directly. Instead, you need to run searches in memory via Java, which is often slower than searching on the database directly.

Answer 2

It's documented in FlexibleSearch Tips and Tricks - Flexible Search and Collections

You have the case of Orders and VoucherCard. It's an exemple where the collection element type has a reference to the item that holds the collection (the vouchercards as a ref to the order)

SELECT {vc.PK}, {vc.price}
FROM
{
   Order AS o JOIN VoucherCard AS vc
   ON {vc.order}= {o.pk}
}
WHERE {o.PK} = ?order

Then you have the case without reference between element and holder. You can use some workaround based on the fact that a collection attribute is stored as a list of PK s:

SELECT {dm.code}, {pm.code}
FROM
{
   DeliveryMode AS dm JOIN PaymentMode AS pm
   ON {dm.supportedpaymentmodes} LIKE CONCAT( '%', CONCAT( {pm.PK} , '%' ) )
}