I have been working on this bug for like 4 days now. I have not been able to figure
it out. Being the last big query of this project, it is kinda a bit harsh a bit late.
What I have tried is to reform the query using:
String query = "SELECT r.rTitle, r.brief FROM rstep1 r, si WHERE ((si.targetmsid =
20001025) AND (si.targetmid = 200000001) AND (si.interestmsid = r.msid))"
QueryBySQL qBySql = QueryFactory.newQuery(objectClass, query);
Collection result = broker.getCollectionByQuery(qBySql);
When I take the resulting query out of the log file and run it against the database,
it works, but OJB returns nothing.
I looked through the archives and saw bugs that might effect this so upgraded from RC4
to last night's nightly build. I still get nothing back from this query. I have no
idea what to do now.
JohnE
----- Original Message -----
From: [EMAIL PROTECTED]
Date: Thursday, March 4, 2004 4:33 pm
Subject: Referencing tables not in return object
>
> I am not sure how to do something and have tried a couple of
> approaches without success. I could use some help.
>
> My problem comes down to the idea that I want to return a mapped
> object but based on a complex query that gets messed up due to the
> way OJB uses foreign keys. Is there a way to do a query on tables
> not referenced in the object you seek to return? I want to
> return rstep1 objects (A0), but I need to create a complex query
> in other tables to get rstep1 objects.
>
>
> What I would to have happened is something like:
> SELECT A0.rTitle, A0.brief
> FROM rstep1 A0, si A1
> WHERE ((A1.targetmsid = 20001025 )
> AND A1.targetmid = 200000001 )
> AND interestmsid = A0.msid
>
>
> What I do get through OJB's use of the foreign key is:
> SELECT A0.rTitle, A0.brief
> FROM rstep1 A0
> INNER JOIN si A1 ON A0.msid=A1.targetmsid
> WHERE ((A1.targetmsid = 20001025 )
> AND A1.targetmid = 200000001 )
> AND A1.interestmsid = A0.msid
>
>
> Is there a way to create a query that returns a mapped object
> where tables referened are not held within the object? I only
> want to return values rstep1, but have to reference another table
> only within the query. The query gets messed up because of the
> foreign key used to generate the query.
>
> I have tried below without using a reference to the si table, but
> si becomes ambigous.
>
> crit.addSql("si.targetmsid = " + jMsid);
> crit.addSql("si.targetmid = " + jMid);
> crit.addSql("si.interestmsid = rstep1.msid");
>
> I have tried below with references to the si table and it creates
> the 2nd query above with the incorrect INNER JOIN.
>
> crit.addEqualTo("si.targetmsid", jMsid);
> crit.addEqualTo("si.targetmid", jMid);
> crit.addEqualToField("si.interestmsid", "memberServiceId");
>
> In this second case, if a reference is required, is there
> a way that I can not have an INNER JOIN on the foreign key be created.
>
>
> Thank you for any help.
>
> JohnE
>
>
>
>
>
>
>
>
> -------------------------------------------------------------------
> --
> 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]
