db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Saravana Kannan <skka...@engr.uky.edu>
Subject Re: left outer join
Date Sun, 02 May 2004 20:11:28 GMT
Hi,

Note: The mail may seem unrelated if you dont read till 
the last line.

  I have a more primitive problem. I have a Torque 
Object called Answer which is related to AnswerGroup 
and ExprNode. Answer can be either the child of 
AnswerGroup OR a child of ExprNode.

  So I do something like-

AnswerGroup ansgrp = new AnswerGroup();
AnswerGroup ans = new Answer();
..
(calling setter methods)
..
ansgrp.save();
ansgrp.addAnswer(ans);
ans.save();

And I get the Torque Exception which is the result of an Oracle constraint 
error. The Oracle error was something of the sort-
"Foreign key 0 does not exist in ExprNode"

The question here is - Why is Torque/Village setting 
ExprNodeFK to "0" when I never called 
ExprNode.addAnswer() or ans.setExprNodeFK() ? I want 
the ExprNodeFK to be null. I'm unable to create foreign 
keys which are null. The relation to the parent 
questions is - How did u manage creating a FK which is 
null? As is the norm, my foreign keys are of type 
INTEGER in the project-schema.xml.

Ofcourse I could create a dummy ExprNode by hand that 
has PK = 0 but is not used anywhere. But I want to find 
the better or proper way to take care of this problem. 
Using ExprNode with PK=0 will be my last resort.

If the problem I mention is the way Torque works, then 
I would like to suggest a solution to this problem as a 
feature of newer releases of Torque. Thank you.

~Sarav

-----Original Message-----
From: T E Schmitz <mailreg@numerixtechnology.de>
To: Apache Torque Users List <torque-user@db.apache.org>
Date: Sun, 02 May 2004 17:43:21 +0100
Subject: left outer join

Hello,

I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND 
and to MODEL via two foreign keys. I would like to select ALL SECTIONs 
whether the FKs are null or not and fetch the BRAND and MODEL attributes 
in one SQL statement. In other words I need a double outer join:

SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_FK,
SECTION.MODEL_FK,
BRAND.BRAND_NAME
MODEL.MODEL_NAME
FROM SECTION
left outer join MODEL  on MODEL_PK =MODEL_FK
left outer join BRAND  on BRAND_PK =BRAND_FK

I have seen some old discussion threads from 2002 suggesting that Torque 
does not support outer joins. Is this still the case?

If so, can anyone suggest an efficient work-around? I'd rather not fetch 
the other table's attributes in a loop.

=======================================================================
2nd Problem
-----------
Scenario as above but this time SECTION has two foreign key 
relationships with the same table:

SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_1_SKIPPED_FK,
SECTION.BRAND_2_SKIPPED_FK,
BRAND.BRAND_NAME (1)
BRAND.BRAND_NAME (2)
FROM SECTION
left outer join BRAND  on BRAND_PK =BRAND_1_SKIPPED_FK
left outer join BRAND  on BRAND_PK =BRAND_2_SKIPPED_FK

(the above obviously doens't work as SQL statement but ...)

How would I specify the same output columns twice?
How can I implement this logic using Torque?

-- 


Regards,

Tarlika Elisabeth Schmitz

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org




-- 
There are 10 kinds of people in the world,
those who know binary and those who don't.


Quoting T E Schmitz <mailreg@numerixtechnology.de>:

> Hello,
> 
> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND 
> and to MODEL via two foreign keys. I would like to select ALL SECTIONs 
> whether the FKs are null or not and fetch the BRAND and MODEL attributes 
> in one SQL statement. In other words I need a double outer join:
> 
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_FK,
> SECTION.MODEL_FK,
> BRAND.BRAND_NAME
> MODEL.MODEL_NAME
> FROM SECTION
> left outer join MODEL  on MODEL_PK =MODEL_FK
> left outer join BRAND  on BRAND_PK =BRAND_FK
> 
> I have seen some old discussion threads from 2002 suggesting that Torque 
> does not support outer joins. Is this still the case?
> 
> If so, can anyone suggest an efficient work-around? I'd rather not fetch 
> the other table's attributes in a loop.
> 
> =======================================================================
> 2nd Problem
> -----------
> Scenario as above but this time SECTION has two foreign key 
> relationships with the same table:
> 
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_1_SKIPPED_FK,
> SECTION.BRAND_2_SKIPPED_FK,
> BRAND.BRAND_NAME (1)
> BRAND.BRAND_NAME (2)
> FROM SECTION
> left outer join BRAND  on BRAND_PK =BRAND_1_SKIPPED_FK
> left outer join BRAND  on BRAND_PK =BRAND_2_SKIPPED_FK
> 
> (the above obviously doens't work as SQL statement but ...)
> 
> How would I specify the same output columns twice?
> How can I implement this logic using Torque?
> 
> -- 
> 
> 
> Regards,
> 
> Tarlika Elisabeth Schmitz
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message