| Description | This is the version of the query we can see coming from the application, when trying to see the organziation drop down list in the filter:
select * from (
select distinct organizati1_.AD_Org_ID as col_0_0_, organizati1_.Name as col_1_0_
from M_Product product0_
left outer join AD_Org organizati1_ on product0_.AD_Org_ID=organizati1_.AD_Org_ID
where (upper(nvl(to_char(organizati1_.Name), '')) like '%PVM%' escape '|')
and (product0_.AD_Org_ID in ('20061' , '20063' , '20062' , '20003' , '20002' , '20023' , '20022' , '20025' , '20001' , '20026' , '20064' , '20065' ,
'20066' , '20067' , '20068' , '20052' , '20051' , '20012' , '20011' , '0' , '20016' , '20015' , '20055' , '20056' , '20053' , '20054'))
and (product0_.AD_Client_ID in ('1969' , '0'))
and product0_.IsActive='Y'
order by organizati1_.Name, organizati1_.AD_Org_ID )
where rownum <= 100 ;
The cost of this query is very high |
| Proposed Solution | The following SQL would improve that cost:
is proposing the following query because the cost is improving:
select * from (
select distinct organizati1_.AD_Org_ID as col_0_0_, organizati1_.Name as col_1_0_
from AD_Org organizati1_
where exists (select 1 from m_product product0_
where (product0_.AD_Org_ID in ('20061' , '20063' , '20062' , '20003' , '20002' , '20023' , '20022' , '20025' , '20001' , '20026' , '20064' ,
'20065' , '20066' , '20067' , '20068' , '20052' , '20051' , '20012' , '20011' , '0' , '20016' , '20015' , '20055' , '20056' , '20053' , '20054'))
and (product0_.AD_Client_ID in ('1969' , '0'))
and product0_.IsActive='Y'
and organizati1_.ad_org_id = product0_.ad_org_id)
and (upper(nvl(to_char(organizati1_.Name), '')) like '%PVM%' escape '|')
order by organizati1_.Name, organizati1_.AD_Org_ID )
where rownum <= 100; |
| Attached Files |  issue-25182.diff [^] (22,119 bytes) 2013-12-17 09:44 [Show Content] [Hide Content]diff -r 08e8dd69188f 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 Mon Dec 16 14:09:22 2013 +0100
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java Tue Dec 17 09:44:18 2013 +0100
@@ -151,6 +151,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;
@@ -201,9 +206,48 @@
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 ";
+ }
+
+ whereClause += StringUtils.isEmpty(whereClause.trim()) ? "where" : "and";
+ whereClause += " exists (select 1 from " + subEntity.getName() + " "
+ + subEntityQueryBuilder.getJoinClause() + subentityWhere + "e."
+ + distinctProperty.getName() + " = " + 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
@@ -1077,7 +1121,7 @@
}
private String getTypedParameterAlias() {
- return ":" + ALIAS_PREFIX + typedParameters.size();
+ return ":" + ALIAS_PREFIX + (typedParameters.size() + aliasOffset);
}
/**
@@ -1491,17 +1535,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() {
if (orNesting > 0) {
return " left outer join " + (fetchJoin ? "fetch " : "")
@@ -1526,17 +1559,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() {
@@ -1648,4 +1673,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 08e8dd69188f 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 Mon Dec 16 14:09:22 2013 +0100
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java Tue Dec 17 09:44:18 2013 +0100
@@ -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;
@@ -60,6 +61,7 @@
public static final String PARAM_DELIMITER = "@";
private String entityName;
+
private Integer firstResult = null;
private Integer maxResults = null;
@@ -132,26 +134,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 +150,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 +199,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 +356,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 08e8dd69188f 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 Mon Dec 16 14:09:22 2013 +0100
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java Tue Dec 17 09:44:18 2013 +0100
@@ -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);
jsonResponse.put(JsonConstants.RESPONSE_TOTALROWS, 1);
return jsonResult.toString();
- } else if (parameters.containsKey(JsonConstants.DISTINCT_PARAMETER)) {
- // TODO: BaseOBObjects created by this query are not valid, see issue #23705, when this is
- // fixed, IdentifierProvider should be revisited to remove code handling this
- // incorrectness
-
- // when distinct an array of values is returned
- // the first value is the BaseObObject the other values
- // are part of the order by and such and can be ignored
- final String distinct = parameters.get(JsonConstants.DISTINCT_PARAMETER);
- final Property distinctProperty = DalUtil.getPropertyFromPath(ModelProvider.getInstance()
- .getEntity(entityName), distinct);
- final Entity distinctEntity = distinctProperty.getTargetEntity();
-
- final List<Property> properties = new ArrayList<Property>();
- properties.addAll(distinctEntity.getIdProperties());
- properties.addAll(queryService.getDistinctDisplayProperties());
-
- // filter the json serialization later on
- final StringBuilder selectedSb = new StringBuilder();
- for (Property prop : properties) {
- if (selectedSb.length() > 0) {
- selectedSb.append(",");
- }
- if (prop.getTargetEntity() != null) {
- // go one level deeper
- final List<Property> nextIdentifierProps = JsonUtils.getIdentifierSet(prop);
- for (Property nextIdentifierProp : nextIdentifierProps) {
- selectedSb.append(prop.getName() + "." + nextIdentifierProp);
- }
- } else {
- selectedSb.append(prop.getName());
- }
- }
- if (selectedProperties == null) {
- selectedProperties = selectedSb.toString();
- } else {
- selectedProperties += "," + selectedSb.toString();
- }
-
- bobs = new ArrayList<BaseOBObject>();
-
- List<List<Property>> cache = new ArrayList<List<Property>>();
- for (Object o : queryService.buildOBQuery().createQuery().list()) {
- final Object[] os = (Object[]) o;
- if (os[0] == null) {
- // the null value is also returned, ignore those
- continue;
- }
-
- if (cache.size() == 0) {
- for (int i = 0; i < os.length; i++) {
- cache.add(null);
- }
- }
-
- // create a BaseOBObject and fill the id/identifier properties
- final BaseOBObject bob = (BaseOBObject) OBProvider.getInstance().get(
- distinctEntity.getName());
- int i = 0;
- for (Property property : properties) {
- // the query contains the identifier and other properties for
- // one level deeper!
- if (property.getTargetEntity() != null) {
- final BaseOBObject refBob = (BaseOBObject) OBProvider.getInstance().get(
- property.getTargetEntity().getName());
- final List<Property> nextIdentifierProps;
- if (cache.get(i) != null) {
- nextIdentifierProps = cache.get(i);
- } else {
- nextIdentifierProps = JsonUtils.getIdentifierSet(property);
- cache.set(i, nextIdentifierProps);
- }
- for (Property nextIdentifierProp : nextIdentifierProps) {
- refBob.setValue(nextIdentifierProp.getName(), os[i++]);
- }
- bob.setValue(property.getName(), refBob);
- } else {
- bob.setValue(property.getName(), os[i++]);
- }
- }
- bobs.add(bob);
- }
} else {
+ long t = System.currentTimeMillis();
bobs = queryService.list();
+ log.debug("query time:" + (System.currentTimeMillis() - t));
}
bobs = bobFetchTransformation(bobs, parameters);
@@ -343,17 +264,92 @@
protected DataEntityQueryService createSetQueryService(Map<String, String> parameters,
boolean forCountOperation) {
- final String entityName = parameters.get(JsonConstants.ENTITYNAME);
+ return createSetQueryService(parameters, forCountOperation, false);
+ }
+
+ private DataEntityQueryService createSetQueryService(Map<String, String> parameters,
+ boolean forCountOperation, boolean forSubEntity) {
+ boolean hasSubentity = false;
+ String entityName = parameters.get(JsonConstants.ENTITYNAME);
+ final DataEntityQueryService queryService = OBProvider.getInstance().get(
+ DataEntityQueryService.class);
+
+ if (!forSubEntity && parameters.get(JsonConstants.DISTINCT_PARAMETER) != null) {
+ // this is the main entity of a 'contains' (used in FK drop down lists), it will create also
+ // info for subentity
+
+ final Property distinctProperty = DalUtil.getPropertyFromPath(ModelProvider.getInstance()
+ .getEntity(entityName), parameters.get(JsonConstants.DISTINCT_PARAMETER));
+ final Entity distinctEntity = distinctProperty.getTargetEntity();
+
+ // criteria needs to be split in two parts:
+ // -One for main entity (the one directly queried for)
+ // -Another one for subentity
+ String baseCriteria = "";
+ String subCriteria = "";
+ hasSubentity = true;
+ if (!StringUtils.isEmpty(parameters.get("criteria"))) {
+ String criteria = parameters.get("criteria");
+ for (String criterion : criteria.split(JsonConstants.IN_PARAMETER_SEPARATOR)) {
+ try {
+ JSONObject jsonCriterion = new JSONObject(criterion);
+ if (jsonCriterion.getString("fieldName").equals(
+ distinctProperty.getName() + "$" + JsonConstants.IDENTIFIER)) {
+ jsonCriterion.put("fieldName", JsonConstants.IDENTIFIER);
+ baseCriteria = jsonCriterion.toString();
+ } else {
+ subCriteria += subCriteria.length() > 0 ? JsonConstants.IN_PARAMETER_SEPARATOR : "";
+ subCriteria += criterion;
+ }
+ } catch (JSONException e) {
+ log.error("Error obtaining 'distint' criterion for " + criterion, e);
+ }
+ }
+ }
+
+ // params for subentity are based on main entity ones
+ @SuppressWarnings("unchecked")
+ Map<String, String> paramSubCriteria = (Map<String, String>) ((HashMap<String, String>) parameters)
+ .clone();
+
+ // set proper criteria for each case
+ if (StringUtils.isEmpty(subCriteria)) {
+ paramSubCriteria.remove("criteria");
+ } else {
+ paramSubCriteria.put("criteria", subCriteria);
+ }
+ if (StringUtils.isEmpty(baseCriteria)) {
+ parameters.remove("criteria");
+ } else {
+ parameters.put("criteria", baseCriteria);
+ }
+
+ // where parameter is only applied in subentity, remove it from main entity
+ if (parameters.containsKey(JsonConstants.WHERE_PARAMETER)) {
+ parameters.remove(JsonConstants.WHERE_PARAMETER);
+ }
+
+ // main entity ("me") settings
+ queryService.getQueryBuilder().setMainAlias("me");
+ queryService.setEntityName(distinctEntity.getName());
+
+ queryService.setFilterOnReadableClients(false);
+ queryService.setFilterOnReadableOrganizations(false);
+ queryService.setFilterOnActive(false);
+
+ // create now subentity
+ queryService.setSubEntity(entityName,
+ createSetQueryService(paramSubCriteria, forCountOperation, true), distinctProperty);
+ } else {
+ queryService.setEntityName(entityName);
+ if (parameters.containsKey(JsonConstants.USE_ALIAS)) {
+ queryService.setUseAlias();
+ }
+ }
+
final String startRowStr = parameters.get(JsonConstants.STARTROW_PARAMETER);
final String endRowStr = parameters.get(JsonConstants.ENDROW_PARAMETER);
- final DataEntityQueryService queryService = OBProvider.getInstance().get(
- DataEntityQueryService.class);
- queryService.setEntityName(entityName);
-
- if (parameters.containsKey(JsonConstants.USE_ALIAS)) {
- queryService.setUseAlias();
- }
boolean directNavigation = parameters.containsKey("_directNavigation")
&& "true".equals(parameters.get("_directNavigation"))
&& parameters.containsKey(JsonConstants.TARGETRECORDID_PARAMETER);
@@ -400,25 +396,24 @@
queryService.setMaxResults(computedMaxResults);
}
- final String sortBy = parameters.get(JsonConstants.SORTBY_PARAMETER);
String orderBy = "";
- if (sortBy != null) {
- orderBy = sortBy;
- } else if (parameters.get(JsonConstants.ORDERBY_PARAMETER) != null) {
- orderBy = parameters.get(JsonConstants.ORDERBY_PARAMETER);
- }
+ if (!hasSubentity) {
+ final String sortBy = parameters.get(JsonConstants.SORTBY_PARAMETER);
+ if (sortBy != null) {
+ orderBy = sortBy;
+ } else if (parameters.get(JsonConstants.ORDERBY_PARAMETER) != null) {
+ orderBy = parameters.get(JsonConstants.ORDERBY_PARAMETER);
+ }
- if (parameters.get(JsonConstants.SUMMARY_PARAMETER) != null
- && parameters.get(JsonConstants.SUMMARY_PARAMETER).trim().length() > 0) {
- queryService.setSummarySettings(parameters.get(JsonConstants.SUMMARY_PARAMETER));
- } else if (parameters.get(JsonConstants.DISTINCT_PARAMETER) != null
- && parameters.get(JsonConstants.DISTINCT_PARAMETER).trim().length() > 0) {
- queryService.setDistinct(parameters.get(JsonConstants.DISTINCT_PARAMETER).trim());
- // sortby the distinct's identifier
- orderBy = getOrderByForDistinct(entityName, queryService);
+ if (parameters.get(JsonConstants.SUMMARY_PARAMETER) != null
+ && parameters.get(JsonConstants.SUMMARY_PARAMETER).trim().length() > 0) {
+ queryService.setSummarySettings(parameters.get(JsonConstants.SUMMARY_PARAMETER));
+ } else {
+ // Always append id to the orderby to make a predictable sorting
+ orderBy += (orderBy.isEmpty() ? "" : ",") + "id";
+ }
} else {
- // Always append id to the orderby to make a predictable sorting
- orderBy += (orderBy.isEmpty() ? "" : ",") + "id";
+ orderBy = JsonConstants.IDENTIFIER;
}
queryService.setOrderBy(orderBy);
@@ -451,26 +446,6 @@
return queryService;
}
- private String getOrderByForDistinct(String entityName, DataEntityQueryService queryService) {
- final String localDistinct = queryService.getDistinct();
- final List<Property> properties = queryService.getDistinctDisplayProperties();
- final StringBuilder sb = new StringBuilder();
- for (Property identifierProp : properties) {
- if (identifierProp.getTargetEntity() != null) {
- // go one level deeper
- final List<Property> nextIdentifierProps = JsonUtils.getIdentifierSet(identifierProp);
- for (Property nextIdentifierProp : nextIdentifierProps) {
- sb.append(localDistinct + DalUtil.DOT + identifierProp.getName() + "."
- + nextIdentifierProp + ",");
- }
- } else {
- sb.append(localDistinct + DalUtil.DOT + identifierProp.getName() + ",");
- }
- }
- sb.append(localDistinct + DalUtil.DOT + JsonConstants.ID);
- return sb.toString();
- }
-
private void addWritableAttribute(List<JSONObject> jsonObjects) throws JSONException {
for (JSONObject jsonObject : jsonObjects) {
if (!jsonObject.has("client") || !jsonObject.has("organization")) {
|
Relationships [ Relation Graph ]
[ Dependency Graph ]
Relationships 