Hi Alen,
You do not have a navigable link from Product to Category right now.
So imo your choices are to:
A. Add a reference-descriptor to add a Category reference to Product
and query for Products directly
<reference-descriptor
name="category"
class-ref="com.baroko.ebiz.model.Category"
auto-retrieve="true"
auto-update="false"
auto-delete="false"
proxy="true" >
<foreignkey field-ref="categoryId" />
</reference-descriptor>
crt.addEqualTo("category.id", new Integer(3));
crt.addEqualTo("activate", new Boolean(true));
Query query = new QueryByCriteria(Product.class, crt, true); //DISTINCT
Collection results = broker.getCollectionByQuery(query);
B. Use the same mapping you currently have, query for the Category like
you are doing, and return the collection reference from the category.
crt.addEqualTo("id", new Integer(3));
crt.addEqualTo("allProductsForCategory.activate", new Boolean(true));
Query query = new QueryByCriteria(Category.class, crt, true); //DISTINCT
Collection results = broker.getCollectionByQuery(query);
Collection products = new Vector();
Iterator itr = results.iterator();
while (itr.hasNext()){
products.addAll(((Category)itr.next()).getAllProductsForCategory());
}
-wally
-----Original Message-----
From: Alen Ribic [mailto:[EMAIL PROTECTED]
Sent: Friday, August 22, 2003 7:20 AM
To: OJB Users List
Subject: Re: Need JOIN
Hi Wally, Others,
This works for me, well I thought it did. ;)
The issue is that for particular category (id: 3) I still get all
products back regardless of there 'activate' boolean state. (I want:
"Give all products for this category where products are activated
(activate=1)" )
p6spy reads:
SELECT DISTINCT A0.description,A0.name,A0.id FROM category A0 INNER JOIN
product A1 ON A0.id=A1.categoryId WHERE (A0.id = '105' ) AND
A1.activate = '1'
If I run above statement with '*' agains my db:
SELECT * FROM category A0 INNER JOIN product A1 ON A0.id=A1.categoryId
WHERE (A0.id = '105' ) AND A1.activate = '1'
I get what I wanna see. All products for category 'id' 3 where
'activate' is set to 1 on products.
my code:
crt.addEqualTo("id", new Integer(3));
crt.addEqualTo("allProductsForCategory.activate", new Integer(1)); Query
queryByCriteria = new QueryByCriteria(Category.class, crt, true);
//DISTINCT
Collection results = broker.getCollectionByQuery(queryByCriteria);
I don't know if this is correct as to what I whish to achieve.
Any help, pls.
--Alen
----- Original Message -----
From: "Gelhar, Wallace Joseph" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Friday, August 22, 2003 3:42 AM
Subject: RE: Need JOIN
Hi Alen,
OJB will handle joins for you. Try something like the following.
crt.addEqualTo("id", new Integer(3));
crt.addEqualTo("allProductsForCategory.activate", new Boolean(true));
Query query = new QueryByCriteria(query, crt, true); //DISTINCT
Collection results = broker.getCollectionByQuery(queryByCriteria);
-Wally
-----Original Message-----
From: Alen Ribic [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 4:38 PM
To: OJB Users List
Subject: Need JOIN
Hi All
I have a single Category object.
This Category object contains a collection of Products of type Vector.
Product objects contain a boolean field named 'activate'.
I have a method that returns a single category with all products in it's
category by category PK. Everything is fine as is but as soon as I try
to specify a product specific criteria, I get an exception that such
column does not exist. Column I wish to use as my criteria (WHERE
clause) is 'activate'.
I was thinking that the category would be joining onto all applicable
products but that doesn't seem to be the case. It must be executing
separate sql statements.
I which to use criteria:
query = "<cat-specific-field>.id = 3 AND <prod-specific-field>.activate
= 1"
as follows:
...
crt.addSql(query);
QueryByCriteria queryByCriteria =
QueryFactory.newQuery(Category.class, crt, true); Collection results
= broker.getCollectionByQuery(queryByCriteria);
my repository <class-descriptor/>'s:
<class-descriptor
class="com.baroko.ebiz.model.Product"
table="product"
>
<field-descriptor id="8"
name="id"
column="id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="9"
name="name"
column="name"
jdbc-type="VARCHAR"
/>
<field-descriptor id="10"
name="price"
column="price"
jdbc-type="FLOAT"
/>
<field-descriptor id="11"
name="stock"
column="stock"
jdbc-type="SMALLINT"
/>
<field-descriptor id="12"
name="description"
column="description"
jdbc-type="VARCHAR"
/>
<field-descriptor id="13"
name="productImage"
column="productImage"
jdbc-type="VARCHAR"
/>
<field-descriptor id="14"
name="activate"
column="activate"
jdbc-type="BIT"
/>
<field-descriptor id="15"
name="categoryId"
column="categoryId"
jdbc-type="SMALLINT"
/>
</class-descriptor>
<class-descriptor
class="com.baroko.ebiz.model.Category"
table="category"
>
<field-descriptor id="16"
name="id"
column="id"
jdbc-type="SMALLINT"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="17"
name="name"
column="name"
jdbc-type="VARCHAR"
/>
<field-descriptor id="18"
name="description"
column="description"
jdbc-type="VARCHAR"
/>
<collection-descriptor
name="allProductsForCategory"
element-class-ref="com.baroko.ebiz.model.Product"
orderby="name"
>
<inverse-foreignkey field-ref="categoryId"/>
</collection-descriptor>
</class-descriptor>
Any help, thx
--Alen
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
