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/05/16 00:23:28 UTC
How to reference a foreign key of a database
Hello,
I have two tables: (excerpt from the xml-schema)
<table name="PRODUCT" idMethod="idbroker">
<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="FLOAT" size="10,2"/>
<column name="IMAGE" required="false" type="VARBINARY"/>
<column name="CATEGORY_ID" required="true" type="INTEGER"/>
<foreign-key foreignTable="CATEGORY">
<reference local="CATEGORY_ID" foreign="ID"/>
</foreign-key>
</table>
<table name="CATEGORY" idMethod="idbroker">
<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>
In the table "PRODUCT" I reference the column "ID" of the other table. Now
I want show all products of a category in a template (when I choose a
category from a menu, the products of that category are supposed to be
shown). But as much as I try, I just can't seem to reference the products
through the category ID (by a foreign key) of the chosen category.
Can anybody please help me with that problem?
Thank you very much,
Sven.
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
Re: How to reference a foreign key of a database (I think, I know the answer)
Posted by Sven Richter <sr...@gmx.net>.
Hello,
I think I found the answer myself. A
"getCategoryRelatedByCategoryId(name)"-function would only be created, if
there is more than one foreign key to another table. Since there is only
one in my schema, that function wasn't created.
Regards,
Sven.
Am Tue, 17 May 2005 17:42:54 +0200 schrieb Sven Richter <sr...@gmx.net>:
> Am Mon, 16 May 2005 18:16:00 +0200 schrieb David Sean Taylor
> <da...@bluesunrise.com>:
>
>> Sven Richter wrote:
>>> Hello,
>>> I have two tables: (excerpt from the xml-schema)
>>> <table name="PRODUCT" idMethod="idbroker">
>>> <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="FLOAT" size="10,2"/>
>>> <column name="IMAGE" required="false" type="VARBINARY"/>
>>> <column name="CATEGORY_ID" required="true" type="INTEGER"/>
>>> <foreign-key foreignTable="CATEGORY">
>>> <reference local="CATEGORY_ID" foreign="ID"/>
>>> </foreign-key>
>>> </table>
>>> <table name="CATEGORY" idMethod="idbroker">
>>> <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>
>>> In the table "PRODUCT" I reference the column "ID" of the other
>>> table. Now I want show all products of a category in a template (when
>>> I choose a category from a menu, the products of that category are
>>> supposed to be shown). But as much as I try, I just can't seem to
>>> reference the products through the category ID (by a foreign key) of
>>> the chosen category.
>>> Can anybody please help me with that problem?
>>
>> Sounds like a question for the Torque list, but I'll try
>> Did you try a criteria query?
>> Something like...
>>
>> Criteria criteria = new Criteria();
>> criteria.add(ProductPeer.CATEGORY_ID, categoryId);
>>
>> List result = ProductPeer.doSelect(criteria);
>>
> Hello,
>
> that's exactly how I did it. The problem was the template. I passed the
> variable for categoryId as an Integer (like: getCategory($cat_Id)),
> instead of as an String (like: like: getCategory("$cat_Id")). The
> problem was, that there is no such error message. You only see that
> nothing happens, that's all.
>
> But I have another question concerning refering to a column of another
> table, that is referenced by a foreign key. I read somewhere, that you
> can do that by using getXRelatedByY(var) (in my case for example:
> getCategoryRelatedByCategoryId(name) to get the NAME of the category in
> the CATEGORY table, using the CATEGORY_ID of the PRODUCT table). But
> when I tried that, I got an error message that the function was not
> found. You can find the article here:
> http://www.developer.com/java/other/article.php/1457081 .
> As of now I used the 'retrieveByPK'-function. It works just fine, if you
> can use the primary key. But if you have more than one foreign key and
> they are not the primary key, you can't use that function.
>
> Thanks you very much,
> Best regards,
> Sven.
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
Re: How to reference a foreign key of a database
Posted by Sven Richter <sr...@gmx.net>.
Am Mon, 16 May 2005 18:16:00 +0200 schrieb David Sean Taylor
<da...@bluesunrise.com>:
> Sven Richter wrote:
>> Hello,
>> I have two tables: (excerpt from the xml-schema)
>> <table name="PRODUCT" idMethod="idbroker">
>> <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="FLOAT" size="10,2"/>
>> <column name="IMAGE" required="false" type="VARBINARY"/>
>> <column name="CATEGORY_ID" required="true" type="INTEGER"/>
>> <foreign-key foreignTable="CATEGORY">
>> <reference local="CATEGORY_ID" foreign="ID"/>
>> </foreign-key>
>> </table>
>> <table name="CATEGORY" idMethod="idbroker">
>> <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>
>> In the table "PRODUCT" I reference the column "ID" of the other table.
>> Now I want show all products of a category in a template (when I
>> choose a category from a menu, the products of that category are
>> supposed to be shown). But as much as I try, I just can't seem to
>> reference the products through the category ID (by a foreign key) of
>> the chosen category.
>> Can anybody please help me with that problem?
>
> Sounds like a question for the Torque list, but I'll try
> Did you try a criteria query?
> Something like...
>
> Criteria criteria = new Criteria();
> criteria.add(ProductPeer.CATEGORY_ID, categoryId);
>
> List result = ProductPeer.doSelect(criteria);
>
Hello,
that's exactly how I did it. The problem was the template. I passed the
variable for categoryId as an Integer (like: getCategory($cat_Id)),
instead of as an String (like: like: getCategory("$cat_Id")). The problem
was, that there is no such error message. You only see that nothing
happens, that's all.
But I have another question concerning refering to a column of another
table, that is referenced by a foreign key. I read somewhere, that you can
do that by using getXRelatedByY(var) (in my case for example:
getCategoryRelatedByCategoryId(name) to get the NAME of the category in
the CATEGORY table, using the CATEGORY_ID of the PRODUCT table). But when
I tried that, I got an error message that the function was not found. You
can find the article here:
http://www.developer.com/java/other/article.php/1457081 .
As of now I used the 'retrieveByPK'-function. It works just fine, if you
can use the primary key. But if you have more than one foreign key and
they are not the primary key, you can't use that function.
Thanks you very much,
Best regards,
Sven.
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org
Re: How to reference a foreign key of a database
Posted by David Sean Taylor <da...@bluesunrise.com>.
Sven Richter wrote:
> Hello,
>
> I have two tables: (excerpt from the xml-schema)
>
> <table name="PRODUCT" idMethod="idbroker">
> <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="FLOAT" size="10,2"/>
> <column name="IMAGE" required="false" type="VARBINARY"/>
> <column name="CATEGORY_ID" required="true" type="INTEGER"/>
> <foreign-key foreignTable="CATEGORY">
> <reference local="CATEGORY_ID" foreign="ID"/>
> </foreign-key>
> </table>
>
> <table name="CATEGORY" idMethod="idbroker">
> <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>
>
> In the table "PRODUCT" I reference the column "ID" of the other table.
> Now I want show all products of a category in a template (when I choose
> a category from a menu, the products of that category are supposed to
> be shown). But as much as I try, I just can't seem to reference the
> products through the category ID (by a foreign key) of the chosen
> category.
>
> Can anybody please help me with that problem?
Sounds like a question for the Torque list, but I'll try
Did you try a criteria query?
Something like...
Criteria criteria = new Criteria();
criteria.add(ProductPeer.CATEGORY_ID, categoryId);
List result = ProductPeer.doSelect(criteria);
--
David Sean Taylor
Bluesunrise Software
david@bluesunrise.com
[office] +01 707 773-4646
[mobile] +01 707 529 9194
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-user-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-user-help@portals.apache.org