details: https://code.openbravo.com/erp/devel/pi/rev/46ec5ec2839d
changeset: 21754:46ec5ec2839d
user: Asier Lostalé <asier.lostale <at> openbravo.com>
date: Tue Jan 07 13:20:26 2014 +0100
summary: fixed bug 25182: FK filter drop down list bad perfomance
Changed the way FK drop down list query is composed
For example filtering organization in product window:
* Before it was:
select distinct o.AD_Org_ID
from M_Product p left join AD_Org o
on p.AD_Org_ID = o.AD_Org_ID
where (//grid filter in product)
* Now it is:
select o.AD_Org_ID
from AD_Org o
where exists (select 1
from M_Product p
where p.AD_Org_ID = o.AD_Org_ID
and (//grid filter in product))
details: https://code.openbravo.com/erp/devel/pi/rev/9c5fe282d29c
changeset: 21755:9c5fe282d29c
user: Asier Lostalé <asier.lostale <at> openbravo.com>
date: Tue Jan 07 13:28:07 2014 +0100
summary: fixed bug 25378: drop down filter doesn't work in FK property columns
diffstat:
modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
| 84 ++-
modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java
| 55 +-
modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java
| 239 ++++-----
3 files changed, 201 insertions(+), 177 deletions(-)
diffs (truncated from 560 to 300 lines):
diff -r 9405040d251c -r 9c5fe282d29c
modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
a/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
Fri Jan 03 14:08:41 2014 +0530
+++
b/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
Tue Jan 07 13:28:07 2014 +0100
@@ -11,7 +11,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2009-2013 Openbravo SLU
+ * All portions are Copyright (C) 2009-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -150,6 +150,11 @@
private boolean joinAssociatedEntities = false;
private List<String> additionalProperties = new ArrayList<String>();
+ private Entity subEntity;
+ private Property distinctProperty;
+ private DataEntityQueryService subDataEntityQueryService;
+
+ private int aliasOffset = 0;
public Entity getEntity() {
return entity;
@@ -200,9 +205,51 @@
whereClause += " ";
+ if (subEntity != null) {
+ // if there's subentity, process it as a subquery with "exists"
+ String subEntityClientOrg = " and e.organization.id "
+ +
createInClause(OBContext.getOBContext().getReadableOrganizations());
+ subEntityClientOrg += " and e.client.id "
+ + createInClause(OBContext.getOBContext().getReadableClients());
+
+ AdvancedQueryBuilder subEntityQueryBuilder =
subDataEntityQueryService.getQueryBuilder();
+ subEntityQueryBuilder.aliasOffset = typedParameters.size();
+
+ String subentityWhere = subEntityQueryBuilder.getWhereClause();
+ if (StringUtils.isEmpty(subentityWhere.trim())) {
+ subentityWhere += " where ";
+ } else {
+ subentityWhere += " and ";
+ }
+
+ String distinctPropName = distinctProperty.getName();
+ if (distinctProperty.isComputedColumn()) {
+ distinctPropName = Entity.COMPUTED_COLUMNS_PROXY_PROPERTY +
DalUtil.DOT + distinctPropName;
+ }
+ whereClause += StringUtils.isEmpty(whereClause.trim()) ? "where" : "and";
+ whereClause += " exists (select 1 from " + subEntity.getName() + " "
+ + subEntityQueryBuilder.getJoinClause() + subentityWhere + "e." +
distinctPropName
+ + " = " + mainAlias + subEntityClientOrg + ") ";
+ typedParameters.addAll(subEntityQueryBuilder.typedParameters);
+ }
+
return whereClause;
}
+ private String createInClause(String[] values) {
+ if (values.length == 0) {
+ return " in ('') ";
+ }
+ final StringBuilder sb = new StringBuilder();
+ for (final String v : values) {
+ if (sb.length() > 0) {
+ sb.append(", ");
+ }
+ sb.append("'" + v + "'");
+ }
+ return " in (" + sb.toString() + ")";
+ }
+
private String addWhereOrgParameters(String where) {
String localWhereClause = where;
// add the organization parameter
@@ -1056,7 +1103,7 @@
}
private String getTypedParameterAlias() {
- return ":" + ALIAS_PREFIX + typedParameters.size();
+ return ":" + ALIAS_PREFIX + (typedParameters.size() + aliasOffset);
}
/**
@@ -1478,17 +1525,6 @@
return checkAlias.equals(ownerAlias) && checkProperty == property;
}
- public String getPropertyPath() {
- if (ownerAlias != null) {
- for (JoinDefinition jd : AdvancedQueryBuilder.this.joinDefinitions) {
- if (jd.getJoinAlias().equals(ownerAlias)) {
- return jd.getPropertyPath() + DalUtil.DOT + property.getName();
- }
- }
- }
- return property.getName();
- }
-
public String getJoinStatement() {
String propName;
if (property.isComputedColumn()) {
@@ -1517,17 +1553,9 @@
this.ownerAlias = ownerAlias;
}
- public boolean isFetchJoin() {
- return fetchJoin;
- }
-
public void setFetchJoin(boolean fetchJoin) {
this.fetchJoin = fetchJoin;
}
-
- public Property getProperty() {
- return property;
- }
}
public String getMainAlias() {
@@ -1639,4 +1667,18 @@
public void setAdditionalProperties(List<String> additionalProperties) {
this.additionalProperties = additionalProperties;
}
+
+ public void setSubEntityName(String subEntityName) {
+ this.subEntity = ModelProvider.getInstance().getEntity(subEntityName);
+ }
+
+ public void setSubDataEntityQueryService(DataEntityQueryService
dataEntityQueryService) {
+ this.subDataEntityQueryService = dataEntityQueryService;
+
+ }
+
+ public void setDistinctProperty(Property distinctProperty) {
+ this.distinctProperty = distinctProperty;
+
+ }
}
diff -r 9405040d251c -r 9c5fe282d29c
modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java
---
a/modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java
Fri Jan 03 14:08:41 2014 +0530
+++
b/modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java
Tue Jan 07 13:28:07 2014 +0100
@@ -11,7 +11,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2009-2013 Openbravo SLU
+ * All portions are Copyright (C) 2009-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -21,6 +21,7 @@
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
+import java.util.Map;
import org.apache.log4j.Logger;
import org.codehaus.jettison.json.JSONException;
@@ -132,26 +133,6 @@
if (getSummarySettings() != null) {
obq.setSelectClause(queryBuilder.getSelectClause());
- } else if (getDistinct() != null) {
- final String localDistinct = getDistinct();
- queryBuilder.addSelectClausePart(localDistinct + ".id");
-
- final List<Property> properties = getDistinctDisplayProperties();
-
- for (Property identifierProp : properties) {
- if (identifierProp.getTargetEntity() != null) {
- // go one level deeper
- final List<Property> nextIdentifierProps =
JsonUtils.getIdentifierSet(identifierProp);
- for (Property nextIdentifierProp : nextIdentifierProps) {
- queryBuilder.addSelectClausePart(localDistinct + DalUtil.DOT +
identifierProp.getName()
- + "." + nextIdentifierProp);
- }
- } else {
- queryBuilder.addSelectClausePart(localDistinct + DalUtil.DOT +
identifierProp.getName());
- }
- }
-
- obq.setSelectClause("distinct " + queryBuilder.getSelectClause());
}
if (getFirstResult() != null) {
@@ -168,11 +149,23 @@
}
obq.setFilterOnActive(isFilterOnActive());
+ if (log.isDebugEnabled()) {
+ String params = "";
+ Map<String, Object> namedParams = queryBuilder.getNamedParameters();
+ for (String paramName : namedParams.keySet()) {
+ params += " -" + paramName + ": " + namedParams.get(paramName) + "\n";
+ }
+ log.debug("Setting params:\n" + params);
+ }
obq.setNamedParameters(queryBuilder.getNamedParameters());
return obq;
}
+ AdvancedQueryBuilder getQueryBuilder() {
+ return queryBuilder;
+ }
+
// package private on purpose
List<Property> getDistinctDisplayProperties() {
final String localDistinct = getDistinct();
@@ -205,9 +198,6 @@
+ queryBuilder.getOrderByClause();
log.debug("Querying for " + entityName + " " + whereOrderBy);
-
- // System.err.println("Querying for " + entityName + " " + whereOrderBy);
-
final OBQuery<BaseOBObject> obq =
OBDal.getInstance().createQuery(entityName, whereOrderBy);
obq.setFilterOnReadableClients(isFilterOnReadableClients());
obq.setFilterOnReadableOrganization(isFilterOnReadableOrganizations());
@@ -365,4 +355,21 @@
this.filterOnReadableClients = filterOnReadableClients;
}
+ /**
+ * In case of performing query for FK drop down list (ie. Organization link
in Product window),
+ * there are 2 entities to query:
+ * <ul>
+ * <li>main entity: in the example would be Organization which is the one we
want to get records
+ * from
+ * <li>sub entity: in this case Product, it will be filtered in the same way
it is in the grid so
+ * only organizations with that criteria will be shown
+ * </ul>
+ */
+ public void setSubEntity(String subEntityName, DataEntityQueryService
dataEntityQueryService,
+ Property distinctProperty) {
+ queryBuilder.setSubEntityName(subEntityName);
+ queryBuilder.setSubDataEntityQueryService(dataEntityQueryService);
+ queryBuilder.setDistinctProperty(distinctProperty);
+ }
+
}
diff -r 9405040d251c -r 9c5fe282d29c
modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java
---
a/modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java
Fri Jan 03 14:08:41 2014 +0530
+++
b/modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java
Tue Jan 07 13:28:07 2014 +0100
@@ -11,7 +11,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2009-2013 Openbravo SLU
+ * All portions are Copyright (C) 2009-2014 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -21,9 +21,11 @@
import java.sql.BatchUpdateException;
import java.util.ArrayList;
import java.util.Collections;
+import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
@@ -60,8 +62,6 @@
public class DefaultJsonDataService implements JsonDataService {
private static final Logger log =
Logger.getLogger(DefaultJsonDataService.class);
- private static final long serialVersionUID = 1L;
-
private static final String ADD_FLAG = "_doingAdd";
private static DefaultJsonDataService instance = new
DefaultJsonDataService();
@@ -105,7 +105,10 @@
boolean preventCountOperation =
!parameters.containsKey(JsonConstants.NOCOUNT_PARAMETER)
|| "true".equals(parameters.get(JsonConstants.NOCOUNT_PARAMETER));
- DataEntityQueryService queryService =
createSetQueryService(parameters, true);
+ @SuppressWarnings("unchecked")
+ Map<String, String> paramsCount = (Map<String, String>)
((HashMap<String, String>) parameters)
+ .clone();
+ DataEntityQueryService queryService =
createSetQueryService(paramsCount, true);
queryService.setEntityName(entityName);
// only do the count if a paging request is done and it has not been
prevented
@@ -140,9 +143,7 @@
jsonResponse.put(JsonConstants.RESPONSE_TOTALROWS, count);
return jsonResponse.toString();
}
-
queryService = createSetQueryService(parameters, false);
- queryService.setEntityName(entityName);
if (parameters.containsKey(JsonConstants.SUMMARY_PARAMETER)) {
final JSONObject singleResult = new JSONObject();
@@ -168,90 +169,10 @@
jsonResponse.put(JsonConstants.RESPONSE_ENDROW, 1);
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
