OFBiz › OFBiz - User

Selecting distinct columns

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 5 messages Options
Varun Bhansaly
Reply | Threaded
Open this post in threaded view
♦
♦
|

Selecting distinct columns

Varun Bhansaly
What is the best way of formulating a query which attempts to select two distinct columns -
For example, the desired query is -

SELECT DISTINCT PF.PRODUCT_FEATURE_TYPE_ID, PF.PRODUCT_FEATURE_CATEGORY_ID FROM PRODUCT_FEATURE PF

The closest which can be achieved is
SELECT DISTINCT (PF.PRODUCT_FEATURE_TYPE_ID, PF.PRODUCT_FEATURE_CATEGORY_ID) FROM PRODUCT_FEATURE PF
this generated query is not acceptable in MySQL but works fine in PostgreSQL

and its generated with this view definition -

<view-entity entity-name="ProductFeatureView" package-name="mypackage">
        <member-entity entity-alias="PF" entity-name="ProductFeature"/>
       
        <alias name="productFeatureTypeAndCategory" entity-alias="PF">
                <complex-alias operator=",">
                        <complex-alias-field entity-alias="PF" field="productFeatureTypeId"/>
                        <complex-alias-field entity-alias="PF" field="productFeatureCategoryId"/>
                </complex-alias>
        </alias>               
</view-entity>

I'm using OFBiz trunk and using delegator.find(...) and delegator.findCountByCondition(...) because the latter does not allow supplying columns to be selected.

Thanks,
Varun Bhansaly
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Selecting distinct columns

Varun Bhansaly
A correction on the last portion - "I'm using OFBiz trunk and using
delegator.find(...) and
NOT using delegator.findCountByCondition(...) because the latter does not
allow supplying columns to be selected."


On Fri, Apr 12, 2013 at 5:40 PM, Varun Bhansaly <[hidden email]> wrote:

> What is the best way of formulating a query which attempts to select two
> distinct columns -
> For example, the desired query is -
>
> SELECT DISTINCT PF.PRODUCT_FEATURE_TYPE_ID, PF.PRODUCT_FEATURE_CATEGORY_ID
> FROM PRODUCT_FEATURE PF
>
> The closest which can be achieved is
> SELECT DISTINCT (PF.PRODUCT_FEATURE_TYPE_ID,
> PF.PRODUCT_FEATURE_CATEGORY_ID)
> FROM PRODUCT_FEATURE PF
> this generated query is not acceptable in MySQL but works fine in
> PostgreSQL
>
> and its generated with this view definition -
>
> <view-entity entity-name="ProductFeatureView" package-name="mypackage">
>         <member-entity entity-alias="PF" entity-name="ProductFeature"/>
>
>         <alias name="productFeatureTypeAndCategory" entity-alias="PF">
>                 <complex-alias operator=",">
>                         <complex-alias-field entity-alias="PF"
> field="productFeatureTypeId"/>
>                         <complex-alias-field entity-alias="PF"
> field="productFeatureCategoryId"/>
>                 </complex-alias>
>         </alias>
> </view-entity>
>
> I'm using OFBiz trunk and using delegator.find(...) and
> delegator.findCountByCondition(...) because the latter does not allow
> supplying columns to be selected.
>
> Thanks,
>
>
>
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Selecting-distinct-columns-tp4640610.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>



--
Regards,
Varun Bhansaly
Varun Bhansaly
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Selecting distinct columns

Varun Bhansaly
Has anyone got something similar working in MySQL (with entity engine)?


On Sat, Apr 13, 2013 at 6:53 AM, Varun Bhansaly <[hidden email]> wrote:

> A correction on the last portion - "I'm using OFBiz trunk and using
> delegator.find(...) and
> NOT using delegator.findCountByCondition(...) because the latter does not
> allow supplying columns to be selected."
>
>
> On Fri, Apr 12, 2013 at 5:40 PM, Varun Bhansaly <[hidden email]>wrote:
>
>> What is the best way of formulating a query which attempts to select two
>> distinct columns -
>> For example, the desired query is -
>>
>> SELECT DISTINCT PF.PRODUCT_FEATURE_TYPE_ID, PF.PRODUCT_FEATURE_CATEGORY_ID
>> FROM PRODUCT_FEATURE PF
>>
>> The closest which can be achieved is
>> SELECT DISTINCT (PF.PRODUCT_FEATURE_TYPE_ID,
>> PF.PRODUCT_FEATURE_CATEGORY_ID)
>> FROM PRODUCT_FEATURE PF
>> this generated query is not acceptable in MySQL but works fine in
>> PostgreSQL
>>
>> and its generated with this view definition -
>>
>> <view-entity entity-name="ProductFeatureView" package-name="mypackage">
>>         <member-entity entity-alias="PF" entity-name="ProductFeature"/>
>>
>>         <alias name="productFeatureTypeAndCategory" entity-alias="PF">
>>                 <complex-alias operator=",">
>>                         <complex-alias-field entity-alias="PF"
>> field="productFeatureTypeId"/>
>>                         <complex-alias-field entity-alias="PF"
>> field="productFeatureCategoryId"/>
>>                 </complex-alias>
>>         </alias>
>> </view-entity>
>>
>> I'm using OFBiz trunk and using delegator.find(...) and
>> delegator.findCountByCondition(...) because the latter does not allow
>> supplying columns to be selected.
>>
>> Thanks,
>>
>>
>>
>> --
>> View this message in context:
>> http://ofbiz.135035.n4.nabble.com/Selecting-distinct-columns-tp4640610.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
>
>
> --
> Regards,
> Varun Bhansaly
>



--
Regards,
Varun Bhansaly
S K Pradeep kumar
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Selecting distinct columns

S K Pradeep kumar
Here is one example

conditions = FastList.newInstance();
conditions.add(EntityCondition.makeCondition("productCategoryId",
EntityOperator.IN, categoryIds));
if (parameters.filter) {
    conditions.add(EntityCondition.makeCondition("brandName",
EntityOperator.LIKE, parameters.filter + "%"));
}
conditions.add(EntityCondition.makeConditionDate("fromDate", "thruDate"));

findOptions = new EntityFindOptions();
findOptions.setDistinct(true);

brandRows = delegator.findList("ProductAndCategoryMember",
EntityCondition.makeCondition(conditions, EntityOperator.AND),
UtilMisc.toSet("brandName"), UtilMisc.toList("brandName"), findOptions,
true);


With regards,
S K Pradeep Kumar,
9035009495


On Sun, Apr 14, 2013 at 8:53 PM, Varun Bhansaly <[hidden email]> wrote:

> Has anyone got something similar working in MySQL (with entity engine)?
>
>
> On Sat, Apr 13, 2013 at 6:53 AM, Varun Bhansaly <[hidden email]>
> wrote:
>
> > A correction on the last portion - "I'm using OFBiz trunk and using
> > delegator.find(...) and
> > NOT using delegator.findCountByCondition(...) because the latter does not
> > allow supplying columns to be selected."
> >
> >
> > On Fri, Apr 12, 2013 at 5:40 PM, Varun Bhansaly <[hidden email]
> >wrote:
> >
> >> What is the best way of formulating a query which attempts to select two
> >> distinct columns -
> >> For example, the desired query is -
> >>
> >> SELECT DISTINCT PF.PRODUCT_FEATURE_TYPE_ID,
> PF.PRODUCT_FEATURE_CATEGORY_ID
> >> FROM PRODUCT_FEATURE PF
> >>
> >> The closest which can be achieved is
> >> SELECT DISTINCT (PF.PRODUCT_FEATURE_TYPE_ID,
> >> PF.PRODUCT_FEATURE_CATEGORY_ID)
> >> FROM PRODUCT_FEATURE PF
> >> this generated query is not acceptable in MySQL but works fine in
> >> PostgreSQL
> >>
> >> and its generated with this view definition -
> >>
> >> <view-entity entity-name="ProductFeatureView" package-name="mypackage">
> >>         <member-entity entity-alias="PF" entity-name="ProductFeature"/>
> >>
> >>         <alias name="productFeatureTypeAndCategory" entity-alias="PF">
> >>                 <complex-alias operator=",">
> >>                         <complex-alias-field entity-alias="PF"
> >> field="productFeatureTypeId"/>
> >>                         <complex-alias-field entity-alias="PF"
> >> field="productFeatureCategoryId"/>
> >>                 </complex-alias>
> >>         </alias>
> >> </view-entity>
> >>
> >> I'm using OFBiz trunk and using delegator.find(...) and
> >> delegator.findCountByCondition(...) because the latter does not allow
> >> supplying columns to be selected.
> >>
> >> Thanks,
> >>
> >>
> >>
> >> --
> >> View this message in context:
> >>
> http://ofbiz.135035.n4.nabble.com/Selecting-distinct-columns-tp4640610.html
> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >>
> >
> >
> >
> > --
> > Regards,
> > Varun Bhansaly
> >
>
>
>
> --
> Regards,
> Varun Bhansaly
>
Varun Bhansaly
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Selecting distinct columns

Varun Bhansaly
Thanks, however this example won't help as it 'selects' only 1 column
(UtilMisc.toSet("brandName")), the requirement is to be able to write
something like this (UtilMisc.toSet("brandName", "productName")).

Also (may be I forgot to mention earlier) the need is to be able to get
count of distinct values, hence instead of using delegator.findList(...)
won't be optimal, would instead prefer
delegator.find(...).getResultsSizeAfterPartialList()
OR delegator.findCountByCondition(...)


On Sun, Apr 14, 2013 at 10:38 PM, S K Pradeep Kumar <
[hidden email]> wrote:

> Here is one example
>
> conditions = FastList.newInstance();
> conditions.add(EntityCondition.makeCondition("productCategoryId",
> EntityOperator.IN, categoryIds));
> if (parameters.filter) {
>     conditions.add(EntityCondition.makeCondition("brandName",
> EntityOperator.LIKE, parameters.filter + "%"));
> }
> conditions.add(EntityCondition.makeConditionDate("fromDate", "thruDate"));
>
> findOptions = new EntityFindOptions();
> findOptions.setDistinct(true);
>
> brandRows = delegator.findList("ProductAndCategoryMember",
> EntityCondition.makeCondition(conditions, EntityOperator.AND),
> UtilMisc.toSet("brandName"), UtilMisc.toList("brandName"), findOptions,
> true);
>
>
> With regards,
> S K Pradeep Kumar,
> 9035009495
>
>
> On Sun, Apr 14, 2013 at 8:53 PM, Varun Bhansaly <[hidden email]>
> wrote:
>
> > Has anyone got something similar working in MySQL (with entity engine)?
> >
> >
> > On Sat, Apr 13, 2013 at 6:53 AM, Varun Bhansaly <[hidden email]>
> > wrote:
> >
> > > A correction on the last portion - "I'm using OFBiz trunk and using
> > > delegator.find(...) and
> > > NOT using delegator.findCountByCondition(...) because the latter does
> not
> > > allow supplying columns to be selected."
> > >
> > >
> > > On Fri, Apr 12, 2013 at 5:40 PM, Varun Bhansaly <[hidden email]
> > >wrote:
> > >
> > >> What is the best way of formulating a query which attempts to select
> two
> > >> distinct columns -
> > >> For example, the desired query is -
> > >>
> > >> SELECT DISTINCT PF.PRODUCT_FEATURE_TYPE_ID,
> > PF.PRODUCT_FEATURE_CATEGORY_ID
> > >> FROM PRODUCT_FEATURE PF
> > >>
> > >> The closest which can be achieved is
> > >> SELECT DISTINCT (PF.PRODUCT_FEATURE_TYPE_ID,
> > >> PF.PRODUCT_FEATURE_CATEGORY_ID)
> > >> FROM PRODUCT_FEATURE PF
> > >> this generated query is not acceptable in MySQL but works fine in
> > >> PostgreSQL
> > >>
> > >> and its generated with this view definition -
> > >>
> > >> <view-entity entity-name="ProductFeatureView"
> package-name="mypackage">
> > >>         <member-entity entity-alias="PF"
> entity-name="ProductFeature"/>
> > >>
> > >>         <alias name="productFeatureTypeAndCategory" entity-alias="PF">
> > >>                 <complex-alias operator=",">
> > >>                         <complex-alias-field entity-alias="PF"
> > >> field="productFeatureTypeId"/>
> > >>                         <complex-alias-field entity-alias="PF"
> > >> field="productFeatureCategoryId"/>
> > >>                 </complex-alias>
> > >>         </alias>
> > >> </view-entity>
> > >>
> > >> I'm using OFBiz trunk and using delegator.find(...) and
> > >> delegator.findCountByCondition(...) because the latter does not allow
> > >> supplying columns to be selected.
> > >>
> > >> Thanks,
> > >>
> > >>
> > >>
> > >> --
> > >> View this message in context:
> > >>
> >
> http://ofbiz.135035.n4.nabble.com/Selecting-distinct-columns-tp4640610.html
> > >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> > >>
> > >
> > >
> > >
> > > --
> > > Regards,
> > > Varun Bhansaly
> > >
> >
> >
> >
> > --
> > Regards,
> > Varun Bhansaly
> >
>



--
Regards,
Varun Bhansaly
Free forum by Nabble Edit this page