Thanks for the pointer. But I have finally figured out what is wrong with my
tables (and no, I didn't make them, I just have to use them). I have a new "example"
to hopefully better illustrate my plight.
There is a 1:1 relationship between SCHOOL and PROFILE. For each school_id in
SCHOOL there is only one profile, sometimes there is NO profile. the 1:n relationship
is between SCHOOL and COLLEGE. How annoying is that. And, how do I now fix this?
----------------------------------------- TABLE SCHOOL int school_id PK int college_id
TABLE PROFILE int profile_id PK int school_id FK to SCHOOL.school_id int profile_type varchar name_desc
TABLE COLLEGE int college_id FK to SCHOOL.college_id int type_of_school -----------------------------------------
And what I need to query is this: ----------------------------------------- SELECT col.college_id, pro.school_id, pro.name_desc FROM COLLEGE AS col INNER JOIN SCHOOL AS sch ON sch.college_id = col.college_id INNER JOIN PROFILE AS pro ON sch.school_id = pro.school_id WHERE pro.profile_type = '1' AND col.type_of_school = '1' ORDER BY pro.name_desc -----------------------------------------
Currently, I have College subclassing School and then a reference from School to
Profile. So queries run against College can have "pro.name_desc" and it works.
But there are multiple school_ids for each college_id. If I change College to
reference School in a <collection-descriptor> I can return all the correct
school_ids in a query. But then I can't query "profile.name_desc" or "pro.profile_type".
So now the joins won't work because joins work through a <reference-descriptor>
and not through a <collection-descriptor>. Or do they?
Should I give up on this nonsense and just do queryBySql()? These tables are
a wreck and I've been battling with them for 2 years. I keep thinking that if
I get this work work I will finally have beaten it.
If anyone can offer any assistance or has had a similar situation I would be extremely greatful. Thanks a lot.
Charlie
Jakob Braeuchi wrote:
hi charles,
if you want to define a 1:n relationship between school (1-side) and profile (n-side) then it's no problem. see the sample " productgroup and articles "
a 1:1 relationship always points to the pk of the referenced class.
hth jakob
Charles N. Harvey III wrote:
Ok. I still can't figure this out. I can guess that since there wasn't a response
that it is an RTFM. But I have been doing that and I still can't see it. I was
hoping that someone could kindly nudge me in the right direction as to how to join
one class to another on any column that is not the primary key.
Thanks again.
Charlie
Charles N. Harvey III wrote:
Can I join to something that is not the PK?
TABLE SCHOOL int school_id PK int college_id
TABLE PROFILE int profile_id PK int school_id FK to SCHOOL.school_id
TABLE INFO int info_id PK int college_id FK to SCHOOL.college_id
Can this be done? It doesn't seem like it. When I try either "super"
or a reference-descriptor foreign-key it always points to the PK of
the referred table. I find this weird since Torque doesn't work this way.
When I setup the torque-schema.xml file you see something like this:
<table name="INFO" javaName="Info" idMethod="none">
<column name="info_id" required="true" primaryKey="true" type="INTEGER" autoIncrement="true"/>
<column name="college_id" required="true" type="INTEGER"/>
<foreign-key foreignTable="SCHOOL">
<reference local="college_id" foreign="college_id"/>
</foreign-key>
</table>
What I like about that setup is that you can designate the local column
and the foreign column. Not so with OJB. Am I just not seeing where it
gets set? This would be huge if it worked.
Oh, and all of the other JOIN questions that I had have been answered. The
latest from CVS made the difference, thanks so much for the tips.
Charlie
--------------------------------------------------------------------- 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]
