cancel
Showing results for 
Search instead for 
Did you mean: 

How to retrive the Count of particular attribute values through flexible search query ?

Former Member
0 Kudos

I am generating a report, which contains "Id, Name & count(id) in a spreadsheet, for this i able to write a query like below:

"select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}"

which is running with out any issues in hAC but if ran it directly, where as through below flexible search code

public List findAllUserCountDetails(){ String query = "select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}"; final FlexibleSearchQuery flexQ = new FlexibleSearchQuery(query); final SearchResult results = flexibleSearchService.search(flexQ); if (results.getResult().isEmpty()) { return null; } return results.getResult(); } We are using the MS SQL Server 2008 for our requirements.

I am getting the below error:

[UserJob::de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob] [172715] translated query was: SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error : An error occurred while converting the nvarchar value to JDBC data type BIGINT. query = 'SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name', values = [8797404758098] INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:384) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:1) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.session.impl.DefaultSessionService.executeInLocalView(DefaultSessionService.java:91) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.getJaloResult(DefaultFlexibleSearchService.java:363) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.search(DefaultFlexibleSearchService.java:164)

INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: de.hybris.platform.jalo.flexiblesearch.FlexibleSearchException: SQL search error : An error occurred while converting the nvarchar value to JDBC data type BIGINT. query = 'SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name', values = [8797404758098][HY-0] INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:169)

INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:169) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:1779) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.compute(FlexibleSearchCacheUnit.java:46) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.privateGetNoLock(AbstractCacheUnit.java:302) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.privateGet(AbstractCacheUnit.java:275) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.get(AbstractCacheUnit.java:182) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.myGet(FlexibleSearchCacheUnit.java:85) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1455) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1628) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:374) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 13 more INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type BIGINT. INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:475) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:2536) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:193) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:132) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2082) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2067) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2328) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jdbcwrapper.ResultSetImpl.getLong(ResultSetImpl.java:243) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$ItemPropertyValueReaderWriter.readValue(JDBCValueMappings.java:892) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$ItemPropertyValueReaderWriter.readValue(JDBCValueMappings.java:1) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$AbstractValueReaderWriter.getValue(JDBCValueMappings.java:533) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.readValue(JDBCValueMappings.java:500) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.readColumns(JDBCValueMappings.java:482) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.getRows(JDBCValueMappings.java:464) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.getQueryResults(JDBCValueMappings.java:309) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:135) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 22 more INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: java.lang.NumberFormatException: For input string: "xyehswanth" INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.NumberFormatException.forInputString(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.Long.parseLong(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.Long.valueOf(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStringToObject(DDC.java:305) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:438) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 37 more

Note: For this requirement i have created my own item type in items xml file. The item type is like below:

itemtype code="UserCounts" deployment table="UserCounts" attributes: uid, name, loginDateTime, logoutDateTime, totalTime, noOfLogins

Can any one please do let me know how to write the correct flexible search query code so that i can get the values like below:

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

here is the code snippet:

     String USER_QUERY = "select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}";

     final FlexibleSearchQuery query = new FlexibleSearchQuery(USER_QUERY);

     query.setResultClassList(Arrays.asList(String.class, String.class,String.class));

     final SearchResult<List<String>> result = flexibleSearchService.search(query);

     final List<List<String>> stringList = result.getResult();

     List<UserDTO> dtosList = new ArrayList<UserDTO>();

     for (final List<String> insideList : stringList) {
         UserDTO dto = new UserDTO();
         dto.setUid(insideList.get(0));
         dto.setName(insideList.get(1));
         dto.setCount(insideList.get(2));            
         dtosList.add(dto);
     }

Answers (4)

Answers (4)

Former Member
0 Kudos

Yes, you are correct, I already achieved my requirement in the same manner. Thank you.

former_member537989
Contributor
0 Kudos

You need to specify result types you are expecting for the query

 final FlexibleSearchQuery flexQ = new FlexibleSearchQuery(query);
 
 query.setResultClassList(Arrays.asList(String.class, String.class,Long.class));
  
 SearchResult<List<Object>> result = flexibleSearchService.search(query);
  
 for (List<Object> row : result.getResult()) {
 String something = row.get(0);
 
 }

Former Member
0 Kudos

Hi Marko,

In my table the records will be like below:

xyestho Yeshwanth 10-01-2016

xyestho Yeshwanth 10-01-2017

xgiri Giri 10-12-2016

In that I want to pull a data like

xyestho Yeshwanth 2

xgiri Giri 1

with the below query,

"select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}"

Where in your response you are pulling only PK's, I am not sure that above solution is going to work for my requirement.

More over i need deal with 1 table only.

Can you please let me know if you have any other insights on this.

Regards, Yesh.

Marko_salonen
Contributor
0 Kudos

Actually you are reading it wrong :) If you look at the code again it clearly does a SQL with count of PK values. So it expects to get a integer and you can see that there is following two lines of code: final List resultClasses = new ArrayList<>(); resultClasses.add(Integer.class);

and that resultClasses is used below.

Marko_salonen
Contributor
0 Kudos

Hi,

if i understand correctly you are trying this query in HAC where it works fine but when you use it in code it fails? This is because flexible search query by default will return items so the first result must always be a PK value. If you would like to have different datatype you should use resultClasses in flexibleSearchQuery. Check for example in de.hybris.platform.product.daos.impl.DefaultProductDao following method :

 @Override
     public Integer findProductsCountByCategory(final CategoryModel category)
     {
         final StringBuilder query = new StringBuilder();
         query.append("SELECT COUNT( {p:" + CategoryModel.PK + "} ) ");
         query.append("FROM {" + ProductModel._TYPECODE + " AS p ");
         query.append("JOIN " + ProductModel._CATEGORYPRODUCTRELATION + "* AS c2pRel ");
         query.append("ON {c2pRel." + Link.TARGET + "}={p:" + CategoryModel.PK + "} } ");
         query.append("WHERE {c2pRel." + Link.SOURCE + "} ");
         query.append(" = ?").append(CategoryModel._TYPECODE);
 
         final List<Class> resultClasses = new ArrayList<>();
         resultClasses.add(Integer.class);
         final Map<String, Object> params = (Map) Collections.singletonMap(CategoryModel._TYPECODE, category);
         final Integer count = (Integer) doSearch(query.toString(), params, resultClasses).iterator().next();
         return count;
     }