Does Flexible Search support for "Select TOP" or Limit?

[Former Member]

Due to performance issue, I want to search for a limit of number of records. I try with "Select top 10000 {p.PK} from {Products as p}", but did not success. Is there an alternative method?

Thanks & Regards, Henry.

[vijay-chandel-sg]

I prepared groovy script to fetch the records based on starting and total count :-

import de.hybris.platform.servicelayer.search.FlexibleSearchService

import de.hybris.platform.servicelayer.search.SearchResult

import com.saintgobain.core.model.MaterialModel

import de.hybris.platform.servicelayer.search.FlexibleSearchQuery

import de.hybris.platform.core.model.product.ProductModel

modelService = spring.getBean("modelService")

final Map<String, Object> params = new HashMap<String, Object>();

params.put("catalogVersion", "Online");

//Get Products only from Online version

final String query = "SELECT {m.pk} FROM {Product as m JOIN Catalogversion as cv ON {cv.pk}={m.catalogversion} JOIN CategoryProductRelation as rel ON {m:PK} = {rel:target} JOIN Category AS c ON {rel:source} = {c:PK}} WHERE {cv.version}=?catalogVersion"

FlexibleSearchService fss = spring.getBean("flexibleSearchService");

final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery(query, params);

//set the starting index

flexibleSearchQuery.setStart(0);

//set the number of records you want to retrive

flexibleSearchQuery.setCount(20)

final SearchResult<ProductModel> searchResult = fss.search(flexibleSearchQuery);

println "Total Products count: "+ searchResult.getTotalCount()

for (int rownum = 0; rownum < searchResult.getTotalCount(); rownum++) {

println "Product Code : "+ searchResult.getResult().get(rownum).getCode()

}