Join tables with many to many relation using GenericSearch

[Former Member]

We have a relation many to many between PointOfService and StoreLocatorFeature and I want to find stores with features from a custom list.

I know how to do it with Flexible Search, but for consistency with other methods in DAO it would be great to implement it using Generic Search. So, can I do it this way and how?

[Former Member]

Hi Svitlana,

Here's a BeanShell script with an example on how to use a relation with GenericQuery. The query searches for StoreLocatorFeature items which have pointOfServices with name name

 import de.hybris.platform.commerceservices.model.storelocator.StoreLocatorFeatureModel;
 import de.hybris.platform.core.GenericCondition;
 import de.hybris.platform.core.GenericQuery;
 import de.hybris.platform.core.GenericSearchField;
 import de.hybris.platform.core.GenericSelectField;
 import de.hybris.platform.core.Operator;
 import de.hybris.platform.storelocator.model.PointOfServiceModel;
 
 genericSearchService = spring.getBean("genericSearchService");
 
 query = new GenericQuery(StoreLocatorFeatureModel._TYPECODE);
 
 storeLocatorFeaturePkField = new GenericSearchField(StoreLocatorFeatureModel._TYPECODE, StoreLocatorFeatureModel.PK);
 relationTargetField = new GenericSearchField("sl2slf", "target");
 storeLocatorFeatureJoinCondition = GenericCondition.createJoinCondition(storeLocatorFeaturePkField, relationTargetField);
 
 pointOfServicePkField = new GenericSearchField(PointOfServiceModel._TYPECODE, PointOfServiceModel.PK);
 relationSourceField = new GenericSearchField("sl2slf", "source");
 pointOfServiceJoinCondition = GenericCondition.createJoinCondition(pointOfServicePkField, relationSourceField);
 
 query.addInnerJoin("StoreLocation2StoreLocatorFeature", "sl2slf", storeLocatorFeatureJoinCondition);
 query.addInnerJoin("PointOfService", pointOfServiceJoinCondition);
 
 nameField = new GenericSearchField(PointOfServiceModel._TYPECODE, PointOfServiceModel.NAME);
 likeCondition = GenericCondition.createConditionForValueComparison(nameField, Operator.LIKE, "name");
 query.addCondition(likeCondition);
 
 selectField = new GenericSelectField(StoreLocatorFeatureModel._TYPECODE, StoreLocatorFeatureModel.CODE, String.class);
 query.addSelectField(selectField);
 
 genericSearchService.search(query).getResult();