db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sven Richter <sr...@gmx.net>
Subject J1.6/Torque: avoiding the return of multiples (the same database entry, several times) with 'setDistinct()']
Date Sat, 23 Jul 2005 20:52:05 GMT
Hello,

I have a problem using Torque with Jetspeed 1.6 and asked that question in the jetspeed-user-list
before. But unfortunatly nobody could help me with it. Someone there suggested 
that I should try my luck here. So, here's my problem:
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). 

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 
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?


Does anybody know, what I can do?

Thanks a lot,
Sven.




---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message