[ 
https://issues.apache.org/jira/browse/OFBIZ-11294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17114708#comment-17114708
 ] 

Amit Gadaley commented on OFBIZ-11294:
--------------------------------------

Hello [~pawan],

 

I have faced the same issue and this solution looks good to me.

I have tested it and it is working fine for me.

> EntityQuery queryCount is throwing error with distinct method
> -------------------------------------------------------------
>
>                 Key: OFBIZ-11294
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-11294
>             Project: OFBiz
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: Release Branch 16.11, Release Branch 18.12, Release 
> Branch 17.12, Trunk
>            Reporter: Pawan Verma
>            Assignee: Pawan Verma
>            Priority: Major
>
> We have a bug/missing support for distinct when used with queryCount method 
> of EntityQuery.
> Below is the more detail
> {code:java}
> EntityCondition cond = EntityCondition.makeCondition(UtilMisc.toList(
>        EntityCondition.makeCondition("locationSeqId", "00test123"),
>        EntityCondition.makeCondition("facilityId", EntityOperator.EQUALS, 
> "10070"),
>        EntityCondition.makeCondition("quantityOnHandTotal", 
> EntityOperator.GREATER_THAN, BigDecimal.ZERO)),
>        EntityOperator.AND);{code}
> *Case 1:* queryList().size() with distinct
> {code:java}
> int productAtLocation = 
> EntityQuery.use(delegator).select("productId").from("InventoryItem")
>        .where(cond).maxRows(2).distinct().queryList().size();{code}
> Result Query: SELECT DISTINCT PRODUCT_ID FROM INVENTORY_ITEM WHERE 
> ((LOCATION_SEQ_ID = ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))
>  Result: This case works well.
> *Case 2:* queryCount without distinct
> {code:java}
> Long testCount = 
> EntityQuery.use(delegator).select("productId").from("InventoryItem")
>        .where(cond).maxRows(2).queryCount();{code}
> Result Query: SELECT COUNT(1)  FROM INVENTORY_ITEM WHERE ((LOCATION_SEQ_ID = 
> ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))
> Result: This case also works well
> *Case 3:*  queryCount with distinct
> {code:java}
> Long testCount = 
> EntityQuery.use(delegator).select("productId").from("InventoryItem")
>        .where(cond).maxRows(2).queryCount();{code}
> Result Query: SELECT COUNT(DISTINCT *)  FROM INVENTORY_ITEM WHERE 
> ((LOCATION_SEQ_ID = ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))
> Result: This case throw an error
>  org.apache.ofbiz.entity.GenericDataSourceException: SQL Exception while 
> executing the following:SELECT COUNT(DISTINCT *) FROM OFBIZ.INVENTORY_ITEM 
> (Syntax error: Encountered "*" at line 1, column 23.)
>  
> *Below is the research I have done for the issue:*
> In EntityQuery.queryCount(), return method is 
> delegator.findCountByCondition(). This method doesn't have support for 
> fieldsToSelect.
>  When we reach till GenericDAO.selectCountByCondition() at line 949, 
> returning method is setting *null* for selectFields.
>  And at the implementation of selectCountByCondition, at line 994 we have a 
> check for selectFields, that's why we are having *COUNT(DISTINCT *)* in our 
> query.
>  
> *To test this, I have used a simple way:*
>  Just include below code at any groovy file and run it:
> {code:java}
> testCount = select("productId").from("InventoryItem").distinct().queryCount()
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Reply via email to