on 01-11-2017 6:41 PM
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:
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);
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, you are correct, I already achieved my requirement in the same manner. Thank you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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);
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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;
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.