Conversion of sql query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Conversion of sql query

Santosh Malviya-3
Hi list,

I have a sql query and I want to do the same with ofbiz entity model.

The query is as follows:
select p.* from product_attribute pa1,product_attribute pa2, product p
where
pa1.attr_name = 'language'
and pa1.attr_value ='english'
and pa2.attr_name = 'genres'
and pa2.attr_value ='Romance' and
pa1.product_id = pa2.product_id
and pa1.product_id = p.product_id

Please help if someone has any idea for doing so. Thanks in advance.




Thanks and Regards
Santosh Malviya
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of sql query

S K Pradeep kumar
Hi Santosh ,

Create the view entity for all your tables and use delegator.findByCondition
method.

With regards,
S K Pradeep kumar,



On Wed, Jan 13, 2010 at 6:05 PM, Santosh Malviya <[hidden email]>wrote:

> Hi list,
>
> I have a sql query and I want to do the same with ofbiz entity model.
>
> The query is as follows:
> select p.* from product_attribute pa1,product_attribute pa2, product p
> where
> pa1.attr_name = 'language'
> and pa1.attr_value ='english'
> and pa2.attr_name = 'genres'
> and pa2.attr_value ='Romance' and
> pa1.product_id = pa2.product_id
> and pa1.product_id = p.product_id
>
> Please help if someone has any idea for doing so. Thanks in advance.
>
>
>
>
> Thanks and Regards
> Santosh Malviya
>
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of sql query

Santosh Malviya-3
Hi Pradeep,
I did the same but in my view entity only one field for each attrName and
attrValue, while I want two different attrName and attrValue, it may be
because of over writing the fields.

Following is my configuration: -

 <view-entity entity-name="ProductAttrAndProductAttrAndProduct"
        package-name="org.ofbiz.product.product"
        title="ProductAttr And ProductAttr And Product View Entity">
        <member-entity entity-alias="PROD" entity-name="Product"/>
        <member-entity entity-alias="PRODATTR1"
entity-name="ProductAttribute"/>
        <member-entity entity-alias="PRODATTR2"
entity-name="ProductAttribute"/>
        <alias-all entity-alias="PROD"/>
        <alias-all entity-alias="PRODATTR1" />
        <alias-all entity-alias="PRODATTR2" />

        <view-link entity-alias="PROD" rel-entity-alias="PRODATTR1">
            <key-map field-name="productId"/>
        </view-link>
        <view-link entity-alias="PRODATTR1" rel-entity-alias="PRODATTR2">
            <key-map field-name="productId"/>
        </view-link>
    </view-entity>


Thanks and Regards
Santosh Malviya

On Wed, Jan 13, 2010 at 6:13 PM, S K Pradeep kumar <
[hidden email]> wrote:

> Hi Santosh ,
>
> Create the view entity for all your tables and use
> delegator.findByCondition
> method.
>
> With regards,
> S K Pradeep kumar,
>
>
>
> On Wed, Jan 13, 2010 at 6:05 PM, Santosh Malviya <[hidden email]
> >wrote:
>
> > Hi list,
> >
> > I have a sql query and I want to do the same with ofbiz entity model.
> >
> > The query is as follows:
> > select p.* from product_attribute pa1,product_attribute pa2, product p
> > where
> > pa1.attr_name = 'language'
> > and pa1.attr_value ='english'
> > and pa2.attr_name = 'genres'
> > and pa2.attr_value ='Romance' and
> > pa1.product_id = pa2.product_id
> > and pa1.product_id = p.product_id
> >
> > Please help if someone has any idea for doing so. Thanks in advance.
> >
> >
> >
> >
> > Thanks and Regards
> > Santosh Malviya
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of sql query

awdesh parihar
Hello Santosh,
See my comment inline.

On Wed, Jan 13, 2010 at 6:34 PM, Santosh Malviya <[hidden email]>wrote:

> Hi Pradeep,
> I did the same but in my view entity only one field for each attrName and
> attrValue, while I want two different attrName and attrValue, it may be
> because of over writing the fields.
>
> Following is my configuration: -
>
>  <view-entity entity-name="ProductAttrAndProductAttrAndProduct"
>        package-name="org.ofbiz.product.product"
>        title="ProductAttr And ProductAttr And Product View Entity">
>        <member-entity entity-alias="PROD" entity-name="Product"/>
>        <member-entity entity-alias="PRODATTR1"
> entity-name="ProductAttribute"/>
>        <member-entity entity-alias="PRODATTR2"
> entity-name="ProductAttribute"/>
>        <alias-all entity-alias="PROD"/>
>        <alias-all entity-alias="PRODATTR1" />
>        <alias-all entity-alias="PRODATTR2" />
>


Try  to use the prefix attribute in alias-all tag
Ex : - <alias-all entity-alias="PRODATTR2" prefix="xyz"/>
HTH
--
--
Thanks & Regards
Awdesh Singh Parihar
Hotwax Media
http://www.hotwaxmedia.com
[hidden email]
-------------------------------------------------
direct: +91 - 989.335.1789
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of sql query

Santosh Malviya-3
Thanks Awdesh, I'll give it a try.

Thanks and Regards
Santosh Malviya

On Wed, Jan 13, 2010 at 6:48 PM, Awdesh Parihar <[hidden email]>wrote:

> Hello Santosh,
> See my comment inline.
>
> On Wed, Jan 13, 2010 at 6:34 PM, Santosh Malviya <[hidden email]
> >wrote:
>
> > Hi Pradeep,
> > I did the same but in my view entity only one field for each attrName and
> > attrValue, while I want two different attrName and attrValue, it may be
> > because of over writing the fields.
> >
> > Following is my configuration: -
> >
> >  <view-entity entity-name="ProductAttrAndProductAttrAndProduct"
> >        package-name="org.ofbiz.product.product"
> >        title="ProductAttr And ProductAttr And Product View Entity">
> >        <member-entity entity-alias="PROD" entity-name="Product"/>
> >        <member-entity entity-alias="PRODATTR1"
> > entity-name="ProductAttribute"/>
> >        <member-entity entity-alias="PRODATTR2"
> > entity-name="ProductAttribute"/>
> >        <alias-all entity-alias="PROD"/>
> >        <alias-all entity-alias="PRODATTR1" />
> >        <alias-all entity-alias="PRODATTR2" />
> >
>
>
> Try  to use the prefix attribute in alias-all tag
> Ex : - <alias-all entity-alias="PRODATTR2" prefix="xyz"/>
> HTH
> --
> --
> Thanks & Regards
> Awdesh Singh Parihar
> Hotwax Media
> http://www.hotwaxmedia.com
> [hidden email]
> -------------------------------------------------
> direct: +91 - 989.335.1789
>