[
https://issues.apache.org/jira/browse/OFBIZ-2976?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Le Roux updated OFBIZ-2976:
-----------------------------------
Description:
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.
{code:xml}
<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>
{code}
{code:xml}
<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>
{code}
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 is
SqlJdbcUtil.makeViewWhereClause. It looks like it's never added conditions
(only joins) going back to v4.0.
was:
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 is
SqlJdbcUtil.makeViewWhereClause. It looks like it's never added conditions
(only joins) going back to v4.0.
> 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
> Components: framework
> Affects Versions: Trunk
> Environment: Java v1.5.0_19-b02-298 MacOS X 10.5.8
> Reporter: Jennifer Weston
> Assignee: Jacques Le Roux
> Priority: Minor
> Attachments: JIRA-Issue2976.patch
>
>
> 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.
> {code:xml}
> <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>
> {code}
> {code:xml}
> <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>
> {code}
> 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 is
> SqlJdbcUtil.makeViewWhereClause. It looks like it's never added conditions
> (only joins) going back to v4.0.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
