How to get all the base products which are having variants in Hybris?

Requirement :

Need to fetch all the base products which are having variants to display those base products in the UI or to perform some logic on those products

There are 2 ways to achieve the above requirement


1) Directly using query, we can get all the base products which are having variants

2) Load all the base products first and then call getVariants() method on each base product object to check whether variants list is empty or not

Note: We should know that, relation between Base Product and its variants is One to Many


Let’s do the implementation in both the ways


1) Using Query

We can write below query to achieve it

select {p.pk} from {Product as p join VariantProduct as vp on {vp.baseProduct}={p.pk}}


We can call this query in DAO layer as below

  1. String query = "select {p.pk} from {Product as p join VariantProduct as vp on {vp.baseProduct}={p.pk}}";
  2.  
  3.         final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
  4.                 searchQuery.setResultClassList(Collections.singletonList(ProductModel.class));
  5.         final SearchResult searchResult = getFlexibleSearchService().search(searchQuery);
  6.         List<ProductModel> products = searchResult.getResult();
  7. return products;
String query = "select {p.pk} from {Product as p join VariantProduct as vp on {vp.baseProduct}={p.pk}}";

		final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
                searchQuery.setResultClassList(Collections.singletonList(ProductModel.class));
		final SearchResult searchResult = getFlexibleSearchService().search(searchQuery);
		List<ProductModel> products = searchResult.getResult();
return products;


2) Using getVariants() method


Getting base products using query and then programtically filter base products having variants using getVariants() method

In this way, we can load List of ProductModel first and We can iterate each product and filter them as below

  1. List<ProductModel> baseProducts = new ArrayList<ProductModel>();
  2. String query = "select {p.pk} FROM {Product as p}"
  3. final SearchResult<ProductModel> searchResult = getFlexibleSearchService().search(query);
  4. for (final ProductModel product : searchResult.getResult()) {
  5.    if(CollectionUtils.isNotEmpty(product.getVariants())) {
  6. baseProducts.add(product);
  7.       }
  8.    }
  9. }
  10. return baseProducts;
List<ProductModel> baseProducts = new ArrayList<ProductModel>();
String query = "select {p.pk} FROM {Product as p}"
final SearchResult<ProductModel> searchResult = getFlexibleSearchService().search(query);
for (final ProductModel product : searchResult.getResult()) {
   if(CollectionUtils.isNotEmpty(product.getVariants())) {
baseProducts.add(product);
      }
   }
}
return baseProducts;

About the Author

Karibasappa G C (KB)
Founder of javainsimpleway.com
I love Java and open source technologies and very much passionate about software development.
I like to share my knowledge with others especially on technology 🙂
I have given all the examples as simple as possible to understand for the beginners.
All the code posted on my blog is developed,compiled and tested in my development environment.
If you find any mistakes or bugs, Please drop an email to kb.knowledge.sharing@gmail.com

Connect with me on Facebook for more updates

Share this article on