view-entity with condition-expr joined with another view-entity as
rel-optional=true is treated as rel-optional=false
---------------------------------------------------------------------------------------------------------------------
Key: OFBIZ-2976
URL: https://issues.apache.org/jira/browse/OFBIZ-2976
Project: OFBiz
Issue Type: Bug
Affects Versions: SVN trunk
Environment: Java v1.5.0_19-b02-298 MacOS X 10.5.8
Reporter: Jennifer Weston
Priority: Minor
Fix For: SVN trunk
If a view-entity is defined with condition-exprs and used as a member-entity in
a second view-entity and the view-link is rel-optional=true, the link is
treated as rel-optional=false.
In the following example, the view-entity PPRMinQty is a list of all
PriceBreakRules that have a minimum quantity defined. The view-entity
ProductPriceBreakMinQty uses PPRMinQty with ProductCategoryMember and
ProductPriceCond (this time used for product categories) to create a list of
productIds with their productPriceRuleIds and their minimum quantities (if
any). The rel-optional=true should mean that even productPriceRuleids that
don't have a minimum quantity defined should be included in the result set.
<view-entity entity-name="PPRMinQty"
package-name="com.mavericklabel"
title="Minimum qty for a product price rule">
<member-entity entity-alias="PPC" entity-name="ProductPriceCond"/>
<alias name="productPriceRuleId" entity-alias="PPC"
field="productPriceRuleId"/>
<alias name="minQty" entity-alias="PPC" field="condValue"/>
<alias name="inputParamEnumId" entity-alias="PPC" field="inputParamEnumId"/>
<alias name="operatorEnumId" entity-alias="PPC" field="operatorEnumId"/>
<entity-condition>
<condition-list>
<condition-expr entity-alias="PPC" field-name="inputParamEnumId"
operator="equals" value="PRIP_QUANTITY"/>
<condition-expr entity-alias="PPC" field-name="operatorEnumId"
operator="equals" value="PRC_GTE"/>
</condition-list>
</entity-condition>
</view-entity>
<view-entity entity-name="ProductPriceBreakMinQty"
package-name="com.mavericklabel"
title="Price breaks for each product with their minimum quantity (min
qty is optional)">
<member-entity entity-alias="PPRCAT" entity-name="ProductPriceCond"/>
<member-entity entity-alias="PCM" entity-name="ProductCategoryMember"/>
<member-entity entity-alias="MINQTY" entity-name="PPRMinQty"/>
<alias name="productId" entity-alias="PCM" field="productId"/>
<alias name="productPriceRuleId" entity-alias="PPRCAT"
field="productPriceRuleId"/>
<alias name="minQty" entity-alias="MINQTY" field="minQty"/>
<alias name="inputParamEnumId" entity-alias="PPRCAT"
field="inputParamEnumId"/>
<alias name="operatorEnumId" entity-alias="PPRCAT" field="operatorEnumId"/>
<view-link entity-alias="PPRCAT" rel-entity-alias="PCM">
<key-map field-name="condValue" rel-field-name="productCategoryId"/>
</view-link>
<view-link entity-alias="PPRCAT" rel-entity-alias="MINQTY"
rel-optional="true">
<key-map field-name="productPriceRuleId"/>
</view-link>
<entity-condition>
<condition-list>
<condition-expr entity-alias="PPRCAT" field-name="inputParamEnumId"
operator="equals" value="PRIP_PROD_CAT_ID"/>
<condition-expr entity-alias="PPRCAT" field-name="operatorEnumId"
operator="equals" value="PRC_EQ"/>
</condition-list>
</entity-condition>
</view-entity>
The resulting SQL looks like this:
SELECT PCM.PRODUCT_ID, PPRCAT.PRODUCT_PRICE_RULE_ID, MINQTY.PPC_COND_VALUE,
PPRCAT.INPUT_PARAM_ENUM_ID, PPRCAT.OPERATOR_ENUM_ID FROM
(OFBIZ.PRODUCT_PRICE_COND PPRCAT INNER JOIN OFBIZ.PRODUCT_CATEGORY_MEMBER PCM
ON PPRCAT.COND_VALUE = PCM.PRODUCT_CATEGORY_ID) LEFT OUTER JOIN (SELECT
PPC.PRODUCT_PRICE_RULE_ID AS PPC_PRODUCT_PRICE_RULE_ID, PPC.COND_VALUE AS
PPC_COND_VALUE, PPC.INPUT_PARAM_ENUM_ID AS PPC_INPUT_PARAM_ENUM_ID,
PPC.OPERATOR_ENUM_ID AS PPC_OPERATOR_ENUM_ID FROM OFBIZ.PRODUCT_PRICE_COND PPC)
MINQTY ON PPRCAT.PRODUCT_PRICE_RULE_ID = MINQTY.PPC_PRODUCT_PRICE_RULE_ID WHERE
((PPRCAT.INPUT_PARAM_ENUM_ID = 'PRIP_PROD_CAT_ID' AND PPRCAT.OPERATOR_ENUM_ID =
'PRC_EQ') AND (PPC.INPUT_PARAM_ENUM_ID = 'PRIP_QUANTITY' AND
PPC.OPERATOR_ENUM_ID = 'PRC_GTE'))
instead of
SELECT PCM.PRODUCT_ID, PPRCAT.PRODUCT_PRICE_RULE_ID, MINQTY.PPC_COND_VALUE,
PPRCAT.INPUT_PARAM_ENUM_ID, PPRCAT.OPERATOR_ENUM_ID FROM
(OFBIZ.PRODUCT_PRICE_COND PPRCAT INNER JOIN OFBIZ.PRODUCT_CATEGORY_MEMBER PCM
ON PPRCAT.COND_VALUE = PCM.PRODUCT_CATEGORY_ID) LEFT OUTER JOIN (SELECT
PPC.PRODUCT_PRICE_RULE_ID AS PPC_PRODUCT_PRICE_RULE_ID, PPC.COND_VALUE AS
PPC_COND_VALUE, PPC.INPUT_PARAM_ENUM_ID AS PPC_INPUT_PARAM_ENUM_ID,
PPC.OPERATOR_ENUM_ID AS PPC_OPERATOR_ENUM_ID FROM OFBIZ.PRODUCT_PRICE_COND PPC
WHERE ((PPC.INPUT_PARAM_ENUM_ID = 'PRIP_QUANTITY' AND PPC.OPERATOR_ENUM_ID =
'PRC_GTE'))) MINQTY ON PPRCAT.PRODUCT_PRICE_RULE_ID =
MINQTY.PPC_PRODUCT_PRICE_RULE_ID WHERE ((PPRCAT.INPUT_PARAM_ENUM_ID =
'PRIP_PROD_CAT_ID' AND PPRCAT.OPERATOR_ENUM_ID = 'PRC_EQ'))
The fact that the conditions from the subquery in the from have been moved to
the top level query create the effect of an inner join even though an outer
join is specified.
The top level where is built by GenericDAO through
ModelViewEntity.populateViewEntityConditionInformation. The change to
ModelViewEntity.populateViewEntityConditionInformation moved this condition to
the top was committed in r805519.
The code that builds the subquery view in the from looks is
SqlJdbcUtil.makeViewWhereClause. It looks like it's never added conditions
(only joins) going back to v4.0.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
