Re: Need JOIN

[Alen Ribic]

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]