You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-user@portals.apache.org by Sven Richter <sr...@gmx.net> on 2005/07/15 22:22:27 UTC
J1.6/Torque: avoiding the return of multiples (the same database
entry, several times) with 'setDistinct()'
Hello,
I have the following three tables:
*<table* name="CATEGORY" idMethod="native"*>*
*<column* name="ID" required="true" primaryKey="true" autoIncrement="true" type="INTEGER"*/>*
*<column* name="NAME" required="true" size="32" type="VARCHAR"*/>*
*<column* name="DESCRIPTION" size="255" type="VARCHAR"*/>*
*<unique* name="CATEGORY_UNIQUE"*>*
*<unique-column* name="NAME"*/>*
*</unique>*
*<index* name="CATEGORY_INDEX"*>*
*<index-column* name="NAME"*/>*
*</index>*
*</table>*
*<table* name="PRODUCT" idMethod="native"*>*
*<column* name="ID" required="true" primaryKey="true" autoIncrement="true" type="INTEGER"*/>*
*<column* name="PRODUCT_NAME" required="true" size="32" type="VARCHAR"*/>*
*<column* name="DESCRIPTION" size="255" type="VARCHAR"*/>*
*<column* name="PRICE" required="true" type="DOUBLE" size="10,2"*/>*
*</table>*
*<table* name="PRODUCT_IN_CATEGORY" idMethod="native"*>*
*<column* name="ID" required="true" primaryKey="true" autoIncrement="true" type="INTEGER"*/>*
/<!-- Produkt-ID -->/
*<column* name="PROD_ID" required="true" type="INTEGER"*/>*
/<!-- Category-ID des Produkts-->/
*<column* name="CAT_ID" required="true" type="INTEGER"*/>*
*<foreign-key* foreignTable="PRODUCT"*>*
*<reference* local="PROD_ID" foreign="ID"*/>*
*</foreign-key>*
*<foreign-key* foreignTable="CATEGORY"*>*
*<reference* local="CAT_ID" foreign="ID"*/>*
*</foreign-key>*
*</table>*
In the table 'PRODUCT' I store the data for the product, except for the
Category. That data is stored in an extra table called
'PRODUCT_IN_CATEGORY', that has a foreign key for the ID in the
'PRODUCT' table and one for the ID in the 'CATEGORY' table. The reason
for that is, that I want to be able to assign a product to more than one
category.
Everything works just fine, except for the fact, that when I don't
specify a certain category in the java class that is responsable for
returning the products by reading them from the database, the products
that are in more than one category, are returned several times (e.g.
when a product is in two categories, the same product is returned
twice). According to the Torque manual, there is an option
'setDistinct()' that is supposed to prevent that. But it somehow does work.
Does anybody know, what I can do?
Thanks a lot,
Sven.
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
Re: J1.6/Torque: avoiding the return of multiples (the same database
entry, several times) with 'setDistinct()'
Posted by Raphaël Luta <ra...@apache.org>.
In case, you haven't done it yet and since it seems like a simple Torque issue,
I think you'll get more Torque knowledgeable people on the torque-dev or
torque-user list over at db.apache.org :)
Sven Richter wrote:
> Hello David,
>
> first of all, thanks a lot for your help.
>
> I use the following function in the OM class of ProductInCategoryPeer
> (with the ID of a category as a parameter):
>
> *public* static *List* getProducts(*String* categoryId) *throws*
> TorqueException
> {
> int iCategoryId = *Integer*.parseInt(categoryId);
> Criteria prodcrit = *new* Criteria();
> prodcrit.setDistinct();
> prodcrit.add(ProductInCategoryPeer.PROD_ID, 0,
> Criteria.GREATER_THAN);
> *if* (iCategoryId != 0)
> {
> prodcrit.add(ProductInCategoryPeer.CAT_ID, iCategoryId);
> }
> prodcrit.addAscendingOrderByColumn(ProductPeer.PRODUCT_NAME);
> prodcrit.setDistinct();
> *return* doSelectJoinProduct(prodcrit);
> }
>
>
> As you can see, I sort the results before I use setDistinct(). But it
> still doesn't work. The function still returns multiples. I already
> tried all possible orders of the lines above, but no change.
>
> Am I doing something wrong?
>
> Thank you very much,
> Sven.
>
> David Pankros wrote:
>
>>> ... According to the Torque manual, there is an option
>>> 'setDistinct()' that is supposed to prevent that. But it somehow does
>>> [not] work.
>>>
>>> Does anybody know, what I can do?
>>>
>>
>>
>> IIRC in standard SQL, select distinct only works if the results are
>> sorted so that the duplicate entries will be next to each other in the
>> result set. (That may be DB specific. I can't remember for sure.)
>> Regardless, it may be affecting you here.
>>
>> I don't know much (if anything) about torque itself, but you may want to
>> try sorting in your query to get those duplicate entries to collapse,
>> while still using the torque setDistinct() option. In your example,
>> sorting by PRODCUT.ID would do it, but remember you also can't select
>> any rows from CATEGORY or PRODUCT_IN_CATEGORY (except maybe a count)
>> otherwise your rows will be no longer distinct and your problem will
>> remain.
>>
>> Hope that helps.
>>
>> Dave
>>
--
Raphaël Luta - raphael@apache.org
Apache Portals - Enterprise Portal in Java
http://portals.apache.org/
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
Re: J1.6/Torque: avoiding the return of multiples (the same database
entry, several times) with 'setDistinct()'
Posted by Sven Richter <sr...@gmx.net>.
Hello David,
first of all, thanks a lot for your help.
I use the following function in the OM class of ProductInCategoryPeer
(with the ID of a category as a parameter):
*public* static *List* getProducts(*String* categoryId) *throws* TorqueException
{
int iCategoryId = *Integer*.parseInt(categoryId);
Criteria prodcrit = *new* Criteria();
prodcrit.setDistinct();
prodcrit.add(ProductInCategoryPeer.PROD_ID, 0, Criteria.GREATER_THAN);
*if* (iCategoryId != 0)
{
prodcrit.add(ProductInCategoryPeer.CAT_ID, iCategoryId);
}
prodcrit.addAscendingOrderByColumn(ProductPeer.PRODUCT_NAME);
prodcrit.setDistinct();
*return* doSelectJoinProduct(prodcrit);
}
As you can see, I sort the results before I use setDistinct(). But it
still doesn't work. The function still returns multiples. I already
tried all possible orders of the lines above, but no change.
Am I doing something wrong?
Thank you very much,
Sven.
David Pankros wrote:
>>... According to the Torque manual, there is an option
>>'setDistinct()' that is supposed to prevent that. But it somehow does
>>[not] work.
>>
>>Does anybody know, what I can do?
>>
>>
>
>IIRC in standard SQL, select distinct only works if the results are
>sorted so that the duplicate entries will be next to each other in the
>result set. (That may be DB specific. I can't remember for sure.)
>Regardless, it may be affecting you here.
>
>I don't know much (if anything) about torque itself, but you may want to
>try sorting in your query to get those duplicate entries to collapse,
>while still using the torque setDistinct() option. In your example,
>sorting by PRODCUT.ID would do it, but remember you also can't select
>any rows from CATEGORY or PRODUCT_IN_CATEGORY (except maybe a count)
>otherwise your rows will be no longer distinct and your problem will
>remain.
>
>Hope that helps.
>
>Dave
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
RE: J1.6/Torque: avoiding the return of multiples (the same database entry, several times) with 'setDistinct()'
Posted by David Pankros <dp...@miragy.com>.
> any rows from CATEGORY or PRODUCT_IN_CATEGORY (except maybe a count)
Make that:
any *columns* from CATEGORY or PRODUCT_IN_CATEGORY (except maybe a
count)
Sorry. :-)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.16/50 - Release Date: 7/15/2005
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
RE: J1.6/Torque: avoiding the return of multiples (the same database entry, several times) with 'setDistinct()'
Posted by David Pankros <dp...@miragy.com>.
> ... According to the Torque manual, there is an option
> 'setDistinct()' that is supposed to prevent that. But it somehow does
> [not] work.
>
> Does anybody know, what I can do?
IIRC in standard SQL, select distinct only works if the results are
sorted so that the duplicate entries will be next to each other in the
result set. (That may be DB specific. I can't remember for sure.)
Regardless, it may be affecting you here.
I don't know much (if anything) about torque itself, but you may want to
try sorting in your query to get those duplicate entries to collapse,
while still using the torque setDistinct() option. In your example,
sorting by PRODCUT.ID would do it, but remember you also can't select
any rows from CATEGORY or PRODUCT_IN_CATEGORY (except maybe a count)
otherwise your rows will be no longer distinct and your problem will
remain.
Hope that helps.
Dave
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.16/50 - Release Date: 7/15/2005
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org