Best way to index multiple values for an entity from Database source
I am looking for some pointers here on how to index and facet based on our goal here.
We have a list of people, who can select multiple characteristics for themselves which then should show up as facets while searching for those people. For example, person 1 can choose multiple values from Happy, Sad, Angry, Frustrated etc. All of this goes to our DB in a special table indexed by userid and moodtype.
Now we would like to bring these mood types into Coveo for searching as facets. A user should be able to select Happy, Sad, Frustrated from the sidebar and see Person 1.
I am trying to understand how this would work with custom fields while indexing.
Do you create a separate field for each mood type and mark Person 1 as having that mood type? For example a custom field called happyMood and set that to Happy or Y for Person 1?
Or do you create a custom field called moodType and set it to a long string containing Happy; Frustrated; etc.?
How does one create a facet in that case? Any help or guidance would be great!
Coveo Facets can only list values of a single field. Thus, you should index all the person's moods in a single CES moodType custom string field and separate the values by a semi-colon like this: "Happy;Sad;Angry;Frustrated".
When creating your custom field in CES, make sure to check the "Allow faceted search on a field containing multiple values" option to tell CES that the content of the field will be semi-colon separated values.
Once the external source is rebuilt and you see the desired values for the field in the CES Index Browser, you will be ready to add your facet. I suppose you want to display that facet in a Coveo for Sitecore search page.
First, you will have to add the moodType field as an external field in Coveo for Sitecore to avoid the field translation (@fmoodtypeXXXXX) (see FieldMap).
<fieldMap type="..."> ... <externalFields hint="raw:AddExternalField"> ... <field fieldName="moodtype"/> </externalFields> </fieldMap>
Then, create a facet on that field in your page/experience editor and you will have all the values as distinct facet values. You may have to check the "Is multi-value field" option in your Coveo for Sitecore facet component properties to have the values split on the semi-colon character.
If the moodtype field is not listed in the "Field" field of the facet component options, you need to add a new facet field definition in Sitecore:
/sitecore/System/Settings/Buckets/Facets. Make sure to use the exact same CES field name in the "Field Name" field of that facet field definition item.
Hope this helps.