You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Chris Rafuse <cr...@cs.dal.ca> on 2003/07/22 15:05:26 UTC
Using Torque to join a table with itself
Hello,
Can anyone tell me if you can join a table with itself using torque? And
if so, maybe a little example? I need to join a table with itself
several times.
Thanks,
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Using Torque to join a table with itself
Posted by Tang <yt...@online.sh.cn>.
You should use Criteria.addJoin method to add join relationship. For
example, I have two tables: MidletSuite and MidletSuireExecutable,
MidletSuiteExecutable has a field (midlet_suite_id) point to MidletSuite
table. Below are my example, hope it will help you.
Criteria criteria = new Criteria();
criteria.addJoin(MidletSuiteExecutablePeer.MIDLET_SUITE_ID,
MidletSuitePeer.ID);
criteria.add(MidletSuitePeer.ID, getId());
List result = MidletSuiteExecutablePeer.doSelect(criteria);
----- Original Message -----
From: "Chris Rafuse" <cr...@cs.dal.ca>
To: "Turbine User Mailing List" <tu...@jakarta.apache.org>
Sent: Wednesday, July 23, 2003 8:44 PM
Subject: Re: Using Torque to join a table with itself
> Hey Keith,
>
> Sorry, not quite. I should have been more specific. Here is the table I
> have:
>
> HouseRooms(int houseId, varchar type, varchar description)
>
> What I want to find out are the homes that have the rooms that I desire.
> So if I wanted a home with a grand bedroom, regular study, and an
> ensuite bathroom, I would have to join the table with itelf many times
> as such:
>
> SELECT distinct houseId
> FROM
> HouseRooms AS Bedroooms,
> HouseRooms AS Bathrooms,
> HouseRooms AS Studies
> WHERE
> Bedrooms.houseId = Bathrooms.houseId
> AND Bathrooms.houseId = Studies.houseId
> AND Bedrooms.type = 'grand'
> AND Bedrooms.description = 'bedroom'
> AND Bathrooms.type = 'ensuite'
> AND Bathrooms.description = 'bathroom'
> AND Studies.type = 'regular'
> AND Studies.description = 'study';
>
>
> This joins the table with itself and produces homes that have ALL of
> these different rooms. I need to do this in torque, but I have no idea
> how. Aliasing maybe?
>
>
> Thanks,
>
>
> Chris
>
> On Tue, 2003-07-22 at 23:19, Keith Seim wrote:
> > I'm not a db expert, but I know torque OM objects that relate to
> > themselves can work like this:
> > (example from my own project)
> >
> > <table name="Exercise" idMethod="idbroker">
> > <column name="ExerciseID" required="true" primaryKey="true"
> > type="INTEGER"/>
> > <column name="Name" required="true" size="80" type="VARCHAR" />
> > <column name="Description" type="LONGVARCHAR" />
> > <column name="InstructionsID" type="INTEGER" />
> > <column name="ParentExerciseID" type="INTEGER" />
> > <column name="Expired" type="TIMESTAMP" />
> > <column name="EntryDeleted" type="TIMESTAMP" />
> > <column name="Tag" size="80" type="VARCHAR" />
> > <column name="CourseID" type="INTEGER" />
> > <column name="DateCreated" type="TIMESTAMP" />
> >
> > <unique><unique-column name="ExerciseID"/></unique>
> >
> > <foreign-key foreignTable="Exercise">
> > <reference local="ParentExerciseID" foreign="ParentExerciseID"/>
> > </foreign-key>
> >
> > <foreign-key foreignTable="Instructions">
> > <reference local="InstructionsID" foreign="InstructionsID"/>
> > </foreign-key>
> >
> > <foreign-key foreignTable="Course">
> > <reference local="CourseID" foreign="CourseID"/>
> > </foreign-key>
> >
> > </table>
> >
> > To see documentation for the resulting OM class, check here:
> >
http://www.kjsdesigns.com/javadoc/ca/utoronto/utm/datasphere/om/BaseExercise
.
> > html
> >
> >
> > So then, all you have to do to get the ParentID object is run the:
> > Exercise getExerciseRelatedByParentexerciseid()
> > method.
> >
> > I'm not sure if this is what you're wanting - if not, please clarify and
> > someone else will help
> >
> > Cheers
> > Keith
> >
> > On Tuesday, July 22, 2003, at 09:05 AM, Chris Rafuse wrote:
> >
> > > Hello,
> > >
> > > Can anyone tell me if you can join a table with itself using torque?
And
> > > if so, maybe a little example? I need to join a table with itself
> > > several times.
> > >
> > > Thanks,
> > >
> > >
> > > Chris
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> > >
> > >
> > >
> > ___________________________________
> > Keith Seim • http://kjsdesigns.com
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Using Torque to join a table with itself
Posted by Bill <bh...@collaborativefusion.com>.
Chris
You should try posting this to the Torque list,
torque-user@db.apache.org. You'll have a much better chance of getting
a solution on that list.
On Wed, 2003-07-23 at 08:44, Chris Rafuse wrote:
> Hey Keith,
>
> Sorry, not quite. I should have been more specific. Here is the table I
> have:
>
> HouseRooms(int houseId, varchar type, varchar description)
>
> What I want to find out are the homes that have the rooms that I desire.
> So if I wanted a home with a grand bedroom, regular study, and an
> ensuite bathroom, I would have to join the table with itelf many times
> as such:
>
> SELECT distinct houseId
> FROM
> HouseRooms AS Bedroooms,
> HouseRooms AS Bathrooms,
> HouseRooms AS Studies
> WHERE
> Bedrooms.houseId = Bathrooms.houseId
> AND Bathrooms.houseId = Studies.houseId
> AND Bedrooms.type = 'grand'
> AND Bedrooms.description = 'bedroom'
> AND Bathrooms.type = 'ensuite'
> AND Bathrooms.description = 'bathroom'
> AND Studies.type = 'regular'
> AND Studies.description = 'study';
>
>
> This joins the table with itself and produces homes that have ALL of
> these different rooms. I need to do this in torque, but I have no idea
> how. Aliasing maybe?
>
>
> Thanks,
>
>
> Chris
>
> On Tue, 2003-07-22 at 23:19, Keith Seim wrote:
> > I'm not a db expert, but I know torque OM objects that relate to
> > themselves can work like this:
> > (example from my own project)
> >
> > <table name="Exercise" idMethod="idbroker">
> > <column name="ExerciseID" required="true" primaryKey="true"
> > type="INTEGER"/>
> > <column name="Name" required="true" size="80" type="VARCHAR" />
> > <column name="Description" type="LONGVARCHAR" />
> > <column name="InstructionsID" type="INTEGER" />
> > <column name="ParentExerciseID" type="INTEGER" />
> > <column name="Expired" type="TIMESTAMP" />
> > <column name="EntryDeleted" type="TIMESTAMP" />
> > <column name="Tag" size="80" type="VARCHAR" />
> > <column name="CourseID" type="INTEGER" />
> > <column name="DateCreated" type="TIMESTAMP" />
> >
> > <unique><unique-column name="ExerciseID"/></unique>
> >
> > <foreign-key foreignTable="Exercise">
> > <reference local="ParentExerciseID" foreign="ParentExerciseID"/>
> > </foreign-key>
> >
> > <foreign-key foreignTable="Instructions">
> > <reference local="InstructionsID" foreign="InstructionsID"/>
> > </foreign-key>
> >
> > <foreign-key foreignTable="Course">
> > <reference local="CourseID" foreign="CourseID"/>
> > </foreign-key>
> >
> > </table>
> >
> > To see documentation for the resulting OM class, check here:
> > http://www.kjsdesigns.com/javadoc/ca/utoronto/utm/datasphere/om/BaseExercise.
> > html
> >
> >
> > So then, all you have to do to get the ParentID object is run the:
> > Exercise getExerciseRelatedByParentexerciseid()
> > method.
> >
> > I'm not sure if this is what you're wanting - if not, please clarify and
> > someone else will help
> >
> > Cheers
> > Keith
> >
> > On Tuesday, July 22, 2003, at 09:05 AM, Chris Rafuse wrote:
> >
> > > Hello,
> > >
> > > Can anyone tell me if you can join a table with itself using torque? And
> > > if so, maybe a little example? I need to join a table with itself
> > > several times.
> > >
> > > Thanks,
> > >
> > >
> > > Chris
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> > >
> > >
> > >
> > ___________________________________
> > Keith Seim • http://kjsdesigns.com
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
--
Bill <bh...@collaborativefusion.com>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Using Torque to join a table with itself
Posted by Chris Rafuse <cr...@cs.dal.ca>.
Hey Keith,
Sorry, not quite. I should have been more specific. Here is the table I
have:
HouseRooms(int houseId, varchar type, varchar description)
What I want to find out are the homes that have the rooms that I desire.
So if I wanted a home with a grand bedroom, regular study, and an
ensuite bathroom, I would have to join the table with itelf many times
as such:
SELECT distinct houseId
FROM
HouseRooms AS Bedroooms,
HouseRooms AS Bathrooms,
HouseRooms AS Studies
WHERE
Bedrooms.houseId = Bathrooms.houseId
AND Bathrooms.houseId = Studies.houseId
AND Bedrooms.type = 'grand'
AND Bedrooms.description = 'bedroom'
AND Bathrooms.type = 'ensuite'
AND Bathrooms.description = 'bathroom'
AND Studies.type = 'regular'
AND Studies.description = 'study';
This joins the table with itself and produces homes that have ALL of
these different rooms. I need to do this in torque, but I have no idea
how. Aliasing maybe?
Thanks,
Chris
On Tue, 2003-07-22 at 23:19, Keith Seim wrote:
> I'm not a db expert, but I know torque OM objects that relate to
> themselves can work like this:
> (example from my own project)
>
> <table name="Exercise" idMethod="idbroker">
> <column name="ExerciseID" required="true" primaryKey="true"
> type="INTEGER"/>
> <column name="Name" required="true" size="80" type="VARCHAR" />
> <column name="Description" type="LONGVARCHAR" />
> <column name="InstructionsID" type="INTEGER" />
> <column name="ParentExerciseID" type="INTEGER" />
> <column name="Expired" type="TIMESTAMP" />
> <column name="EntryDeleted" type="TIMESTAMP" />
> <column name="Tag" size="80" type="VARCHAR" />
> <column name="CourseID" type="INTEGER" />
> <column name="DateCreated" type="TIMESTAMP" />
>
> <unique><unique-column name="ExerciseID"/></unique>
>
> <foreign-key foreignTable="Exercise">
> <reference local="ParentExerciseID" foreign="ParentExerciseID"/>
> </foreign-key>
>
> <foreign-key foreignTable="Instructions">
> <reference local="InstructionsID" foreign="InstructionsID"/>
> </foreign-key>
>
> <foreign-key foreignTable="Course">
> <reference local="CourseID" foreign="CourseID"/>
> </foreign-key>
>
> </table>
>
> To see documentation for the resulting OM class, check here:
> http://www.kjsdesigns.com/javadoc/ca/utoronto/utm/datasphere/om/BaseExercise.
> html
>
>
> So then, all you have to do to get the ParentID object is run the:
> Exercise getExerciseRelatedByParentexerciseid()
> method.
>
> I'm not sure if this is what you're wanting - if not, please clarify and
> someone else will help
>
> Cheers
> Keith
>
> On Tuesday, July 22, 2003, at 09:05 AM, Chris Rafuse wrote:
>
> > Hello,
> >
> > Can anyone tell me if you can join a table with itself using torque? And
> > if so, maybe a little example? I need to join a table with itself
> > several times.
> >
> > Thanks,
> >
> >
> > Chris
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
> >
> >
> ___________________________________
> Keith Seim • http://kjsdesigns.com
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Using Torque to join a table with itself
Posted by Keith Seim <ke...@kjsdesigns.com>.
I'm not a db expert, but I know torque OM objects that relate to
themselves can work like this:
(example from my own project)
<table name="Exercise" idMethod="idbroker">
<column name="ExerciseID" required="true" primaryKey="true"
type="INTEGER"/>
<column name="Name" required="true" size="80" type="VARCHAR" />
<column name="Description" type="LONGVARCHAR" />
<column name="InstructionsID" type="INTEGER" />
<column name="ParentExerciseID" type="INTEGER" />
<column name="Expired" type="TIMESTAMP" />
<column name="EntryDeleted" type="TIMESTAMP" />
<column name="Tag" size="80" type="VARCHAR" />
<column name="CourseID" type="INTEGER" />
<column name="DateCreated" type="TIMESTAMP" />
<unique><unique-column name="ExerciseID"/></unique>
<foreign-key foreignTable="Exercise">
<reference local="ParentExerciseID" foreign="ParentExerciseID"/>
</foreign-key>
<foreign-key foreignTable="Instructions">
<reference local="InstructionsID" foreign="InstructionsID"/>
</foreign-key>
<foreign-key foreignTable="Course">
<reference local="CourseID" foreign="CourseID"/>
</foreign-key>
</table>
To see documentation for the resulting OM class, check here:
http://www.kjsdesigns.com/javadoc/ca/utoronto/utm/datasphere/om/BaseExercise.
html
So then, all you have to do to get the ParentID object is run the:
Exercise getExerciseRelatedByParentexerciseid()
method.
I'm not sure if this is what you're wanting - if not, please clarify and
someone else will help
Cheers
Keith
On Tuesday, July 22, 2003, at 09:05 AM, Chris Rafuse wrote:
> Hello,
>
> Can anyone tell me if you can join a table with itself using torque? And
> if so, maybe a little example? I need to join a table with itself
> several times.
>
> Thanks,
>
>
> Chris
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
>
___________________________________
Keith Seim • http://kjsdesigns.com
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org