Hi Ben,
if you create table/class 'Category' with primary key 'categoryid' and reference
'categoryid' by 'product.categoryid' and 'productheader.categoryid' it should be
possible to define a mapping with castor. A OQL similar to
LEFT OUTER JOIN productheader ON
product.categoryid=productheader.categoryid
should be possible with this solution.
Regards
Ralf
----- Original Message -----
From: "Ben Christensen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 10, 2003 9:08 PM
Subject: [castor-dev] Use Different Object Key on Many-Key (Left Outer Join)
> Hi,
>
> Castor is working wonderfully at loading some fairly complex
> relationships, however, I've come across something that I don't think
> Castor can handle without me changing the database structure
> substantially.
>
> I have a Product table (structure is found below) whose Key is
> productid. This Product table (and object) have several objects such as
> DataSources that are pulled in a collection based upon that productid
> using the many-key element. This works fine.
>
> However, I have a situation where there is a referencing table for
> product headers that uses the Product.categoryid key instead of
> Product.productid.
>
> In the SQL statement right here you'll see that using a normal
> "many-key" approach, it assumes that Product.productid will always be
> used (LEFT OUTER JOIN productheader ON
> product.productid=productheader.categoryid)
>
> SELECT
> product.description,product.mfgpartno,product.categoryid,product.manufac
> turerid,productdatasources.productdatasourcesid,productheader.producthea
> derid FROM product LEFT OUTER JOIN productdatasources ON
> product.productid=productdatasources.productid LEFT OUTER JOIN
> productheader ON product.productid=productheader.categoryid WHERE
> product.productid=?
>
> What I need it to do is let me change the many-key slightly so the sql
> becomes:
>
> LEFT OUTER JOIN productheader ON
> product.categoryid=productheader.categoryid
>
> I have not yet determined a way to do this with Castor -- since Castor
> always seems to do the join with the Object key defined by 'identity'
> in the class element -- and the many-key element doesn't seem to have
> any arguments to change that.
>
> <sql many-key="categoryid">
>
> I would like to do:
>
> <sql many-key="categoryid" name="categoryid"> to override what is the
> default <sql many-key="categoryid" name="productid">
>
> though I'd call the name element something different in this case, such
> as many-key-id.
>
>
>
> The reason this is done in the database is because if I were to use the
> ProductID for the ProductHeader table, instead of CategoryID, the
> Header table would be exponentially bigger than it needs to be, having
> duplicate entries for thousand of products, rather that just the
> several hundred categories that those products fall under. This is a
> simple scenario using JDBC SQL -- could you shine some light please on
> how to implement this with Castor.
>
> I appreciate your advice in advance.
>
> Ben
>
>
>
>
>
>
> ********* Mapping File for Product, DataSource, and Header objects
> ************
>
> Note: I have not included mappings for object that are not important to
> the question at hand ... such as Category, Manufacturer,
> AccessoryProducts etc.
>
>
>
> <class name="com.company.beans.content.ProductDTO" identity="id"
> key-generator="MAX" read-only="true">
> <cache-type type="count-limited" capacity="500"/>
> <map-to table="product" />
>
> <field name="id" type="integer">
> <sql name="productid" type="integer" />
> </field>
>
> <field name="description" type="string">
> <sql name="description" type="char" />
> </field>
>
> <field name="mfgPartNumber" type="string">
> <sql name="mfgpartno" type="char" />
> </field>
>
> <field name="category"
> type="com.company.beans.content.CategoryDTO">
> <sql name="categoryid"/>
> </field>
>
> <field name="manufacturer"
> type="com.company.beans.content.ManufacturerDTO">
> <sql name="manufacturerid"/>
> </field>
>
> <!-- Load all dataSource to go with it -->
> <field name="dataSource"
> type="com.company.beans.content.ProductDataSourceDTO" required="true"
> collection="arraylist" set-method="setDataSources"
> get-method="getDataSources">
> <sql many-key="productid" />
> </field>
>
> <!-- Load all headers to go with it -->
> <field name="headers"
> type="com.company.beans.content.ProductHeaderDTO" required="true"
> collection="arraylist" set-method="setHeaders" get-method="getHeaders">
> <sql many-key="categoryid" />
> </field>
>
> <!-- Load all accessoryProducts to go with it
> <field name="accessoryProduct"
> type="com.company.beans.content.AccessoryProductDTO" required="true"
> collection="arraylist" set-method="setAccessories"
> get-method="getAccessories">
> <sql many-key="productid" />
> </field>
> -->
> </class>
>
>
>
>
>
> <class name="com.company.beans.content.ProductDataSourceDTO"
> identity="id" key-generator="MAX">
> <cache-type type="unlimited"/>
> <map-to table="productdatasources" />
>
> <field name="id" type="integer">
> <sql name="productdatasourcesid" type="integer" />
> </field>
>
> <field name="productid" type="integer">
> <sql name="productid" type="integer" />
> </field>
>
> <field name="code" type="string">
> <sql name="datasourcesku" type="char" />
> </field>
>
> <field name="name" type="string">
> <sql name="datasource" type="char" />
> </field>
>
> </class>
>
>
>
>
>
> <class name="com.company.beans.content.ProductHeaderDTO"
> identity="id" key-generator="MAX">
> <cache-type type="unlimited"/>
> <map-to table="productheader" />
>
> <field name="id" type="integer">
> <sql name="productheaderid" type="integer" />
> </field>
>
> <field name="name" type="string">
> <sql name="name" type="char" />
> </field>
>
> <field name="categoryID" type="integer">
> <sql name="categoryid" type="integer" />
> </field>
>
> <!-- Load all accessoryProducts to go with it -->
> <field name="attributes"
> type="com.company.beans.content.ProductAttributeDTO" required="true"
> collection="arraylist" set-method="setAttributes"
> get-method="getAttributes">
> <sql many-key="templateheaderid" />
> </field>
>
> </class>
>
>
> ****** Table Structure for product, productdatasources, and
> productheader ***********
>
>
> CREATE TABLE product (
> productid int(10) unsigned NOT NULL default '0',
> manufacturerid int(10) unsigned NOT NULL default '0',
> isactive tinyint(1) NOT NULL default '0',
> mfgpartno varchar(25) NOT NULL default '',
> description text NOT NULL,
> productstatusid tinyint(4) NOT NULL default '0',
> categoryid int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (productid),
> INDEX (manufacturerid),
> INDEX (categoryid)
> ) TYPE=InnoDB;
>
>
>
> CREATE TABLE productdatasources (
> productdatasourcesid int(10) unsigned NOT NULL default '0',
> productid int(10) unsigned NOT NULL default '0',
> datasource varchar(60) binary default NULL,
> datasourcesku varchar(60) binary default NULL,
> PRIMARY KEY (productdatasourcesid),
> INDEX (productid)
> ) TYPE=InnoDB;
>
>
> CREATE TABLE productheader (
> productheaderid int(10) unsigned NOT NULL default '0',
> templateheaderid int(10) unsigned NOT NULL default '0',
> name varchar(100) binary default NULL,
> categoryid int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (productheaderid),
> INDEX (templateheaderid),
> INDEX (categoryid)
> ) TYPE=InnoDB;
>
>
>
>
> ---------------------------------------------
> Ben Christensen
>
> -----------------------------------------------------------
> If you wish to unsubscribe from this mailing, send mail to
> [EMAIL PROTECTED] with a subject of:
> unsubscribe castor-dev
>
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
