OFBiz › OFBiz - User

FW: Creating DynamicViewEntity !

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 4 messages Options
prashant punekar
Reply | Threaded
Open this post in threaded view
♦
♦
|

FW: Creating DynamicViewEntity !

prashant punekar
Hi,

Sorry for resending again! Need urgent help on this.

How to create the following query using DynamicViewEntity?

SELECT pcm.product_id, pp.price
FROM product_category_member pcm
INNER JOIN product_price pp ON (
pcm.product_id = pp.product_id AND price BETWEEN 15 AND 20)
WHERE pcm.product_category_id IN ('100', '101')

Thanx in advance for the help.

Regards,
Prashant


________________________________
http://www.mindtree.com/email/disclaimer.html
awdesh parihar
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: FW: Creating DynamicViewEntity !

awdesh parihar
Hello Prashant ,

You can refer following code to create dynamic view entity .

DynamicViewEntity dynamicView = new DynamicViewEntity();
dynamicView.addMemberEntity("PCM", "ProductCategoryMember");
dynamicView.addAliasAll("PCM", "")
dynamicView.addMemberEntity("PP", 'ProductPrice');
dynamicView.addAliasAll("PP" , "")
dynamicView.addViewLink("PCM", "PP", Boolean.TRUE,
        ModelKeyMap.makeKeyMapList("productId"));

>
>
> pcm.product_id = pp.product_id AND price BETWEEN 15 AND 20)


> WHERE pcm.product_category_id IN ('100', '101')


For this put condition on dynamic view entity (for reference prefer
findParty service)

--
Awdesh Parihar
prashant punekar
Reply | Threaded
Open this post in threaded view
♦
♦
|

RE: FW: Creating DynamicViewEntity !

prashant punekar
Hi Awdesh,

Your reply is correct for the given query.
I can move the condition (AND price BETWEEN 15 AND 20) to where clause but I have other queries where I need the condition to be part of
INNER JOIN ON (condtion). In that case how do I do it?

Here is the main query to be generated with dynamic entity view:

SELECT pcm.product_id,
PRICE_FLAT_AMOUNT.amount PRICE_FLAT_AMOUNT,
PRICE_FOL_AMOUNT.amount PRICE_FOL_AMOUNT,
PRICE_PFLAT_AMOUNT.amount PRICE_PFLAT_AMOUNT,
PRICE_POAC_AMOUNT.amount PRICE_POAC_AMOUNT,
PRICE_POD_AMOUNT.amount PRICE_POD_AMOUNT,
PRICE_POL_AMOUNT.amount PRICE_POL_AMOUNT,
PRICE_POM_AMOUNT.amount PRICE_POM_AMOUNT,
PRICE_WFLAT_AMOUNT.amount PRICE_WFLAT_AMOUNT
FROM product_category_member pcm
INNER JOIN product_price_cond ppc ON (
pcm.product_id = ppc.cond_value AND ppc.input_param_enum_id = 'PRIP_PRODUCT_ID')
INNER JOIN product_price_action PRICE_FLAT_AMOUNT ON (
PRICE_FLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FLAT' AND PRICE_FLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_FOL_AMOUNT ON (
PRICE_FOL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FOL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FOL' AND PRICE_FOL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_PFLAT_AMOUNT ON (
PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_PFLAT' AND PRICE_PFLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POAC_AMOUNT ON (
PRICE_POAC_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POAC_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POAC' AND PRICE_POAC_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POD_AMOUNT ON (
PRICE_POD_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POD_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POD' AND PRICE_POD_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POL_AMOUNT ON (
PRICE_POL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POL' AND PRICE_POL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POM_AMOUNT ON (
PRICE_POM_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POM_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POM' AND PRICE_POM_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_WFLAT_AMOUNT ON (
PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_WFLAT' AND PRICE_WFLAT_AMOUNT.amount BETWEEN 15 AND 20)
WHERE pcm.product_category_id IN ('100','101','102','201','20111','202','CATALOG1_QUICKADD1','CATALOG1_QUICKADD2')

The above between condition can also be re-written as:
PRICE_POM_AMOUNT.amount >= 15 AND PRICE_POM_AMOUNT.amount <= 20

Request you to help me in creating the above query using DVE.

Thanks in advance!

Regards,
Prashant

-----Original Message-----
From: Awdesh Parihar [mailto:[hidden email]]
Sent: Friday, August 28, 2009 3:27 PM
To: [hidden email]
Subject: Re: FW: Creating DynamicViewEntity !

Hello Prashant ,

You can refer following code to create dynamic view entity .

DynamicViewEntity dynamicView = new DynamicViewEntity();
dynamicView.addMemberEntity("PCM", "ProductCategoryMember");
dynamicView.addAliasAll("PCM", "")
dynamicView.addMemberEntity("PP", 'ProductPrice');
dynamicView.addAliasAll("PP" , "")
dynamicView.addViewLink("PCM", "PP", Boolean.TRUE,
        ModelKeyMap.makeKeyMapList("productId"));

>
>
> pcm.product_id = pp.product_id AND price BETWEEN 15 AND 20)


> WHERE pcm.product_category_id IN ('100', '101')


For this put condition on dynamic view entity (for reference prefer
findParty service)

--
Awdesh Parihar

http://www.mindtree.com/email/disclaimer.html
prashant punekar
Reply | Threaded
Open this post in threaded view
♦
♦
|

FW: FW: Creating DynamicViewEntity !

prashant punekar
In reply to this post by prashant punekar
Hi All,

Currently the Dynamic Entity View (DVE) doesn't help me in creating the query listed below because of the query condition (AND condition) in the JOINS.

For this requirement do you suggest me to change the current Entity Engine's DVE implementation to take care of this? Or is there any other better approach to be taken to resolve this issue.

Regards,
Prashant

-----Original Message-----
From: Prashant Punekar
Sent: Friday, August 28, 2009 3:39 PM
To: '[hidden email]'
Subject: RE: FW: Creating DynamicViewEntity !

Hi Awdesh,

Your reply is correct for the given query.
I can move the condition (AND price BETWEEN 15 AND 20) to where clause but I have other queries where I need the condition to be part of
INNER JOIN ON (condtion). In that case how do I do it?

Here is the main query to be generated with dynamic entity view:

SELECT pcm.product_id,
PRICE_FLAT_AMOUNT.amount PRICE_FLAT_AMOUNT,
PRICE_FOL_AMOUNT.amount PRICE_FOL_AMOUNT,
PRICE_PFLAT_AMOUNT.amount PRICE_PFLAT_AMOUNT,
PRICE_POAC_AMOUNT.amount PRICE_POAC_AMOUNT,
PRICE_POD_AMOUNT.amount PRICE_POD_AMOUNT,
PRICE_POL_AMOUNT.amount PRICE_POL_AMOUNT,
PRICE_POM_AMOUNT.amount PRICE_POM_AMOUNT,
PRICE_WFLAT_AMOUNT.amount PRICE_WFLAT_AMOUNT
FROM product_category_member pcm
INNER JOIN product_price_cond ppc ON (
pcm.product_id = ppc.cond_value AND ppc.input_param_enum_id = 'PRIP_PRODUCT_ID')
INNER JOIN product_price_action PRICE_FLAT_AMOUNT ON (
PRICE_FLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FLAT' AND PRICE_FLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_FOL_AMOUNT ON (
PRICE_FOL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_FOL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_FOL' AND PRICE_FOL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_PFLAT_AMOUNT ON (
PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_PFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_PFLAT' AND PRICE_PFLAT_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POAC_AMOUNT ON (
PRICE_POAC_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POAC_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POAC' AND PRICE_POAC_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POD_AMOUNT ON (
PRICE_POD_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POD_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POD' AND PRICE_POD_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POL_AMOUNT ON (
PRICE_POL_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POL_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POL' AND PRICE_POL_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_POM_AMOUNT ON (
PRICE_POM_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_POM_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_POM' AND PRICE_POM_AMOUNT.amount BETWEEN 15 AND 20)
INNER JOIN product_price_action PRICE_WFLAT_AMOUNT ON (
PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_RULE_ID = ppc.PRODUCT_PRICE_RULE_ID
AND PRICE_WFLAT_AMOUNT.PRODUCT_PRICE_ACTION_TYPE_ID = 'PRICE_WFLAT' AND PRICE_WFLAT_AMOUNT.amount BETWEEN 15 AND 20)
WHERE pcm.product_category_id IN ('100','101','102','201','20111','202','CATALOG1_QUICKADD1','CATALOG1_QUICKADD2')

The above between condition can also be re-written as:
PRICE_POM_AMOUNT.amount >= 15 AND PRICE_POM_AMOUNT.amount <= 20

Request you to help me in creating the above query using DVE.

Thanks in advance!

Regards,
Prashant

-----Original Message-----
From: Awdesh Parihar [mailto:[hidden email]]
Sent: Friday, August 28, 2009 3:27 PM
To: [hidden email]
Subject: Re: FW: Creating DynamicViewEntity !

Hello Prashant ,

You can refer following code to create dynamic view entity .

DynamicViewEntity dynamicView = new DynamicViewEntity();
dynamicView.addMemberEntity("PCM", "ProductCategoryMember");
dynamicView.addAliasAll("PCM", "")
dynamicView.addMemberEntity("PP", 'ProductPrice');
dynamicView.addAliasAll("PP" , "")
dynamicView.addViewLink("PCM", "PP", Boolean.TRUE,
        ModelKeyMap.makeKeyMapList("productId"));

>
>
> pcm.product_id = pp.product_id AND price BETWEEN 15 AND 20)


> WHERE pcm.product_category_id IN ('100', '101')


For this put condition on dynamic view entity (for reference prefer
findParty service)

--
Awdesh Parihar

http://www.mindtree.com/email/disclaimer.html
Free forum by Nabble Edit this page