I didn't go to sql, that was just the representation of the sql that OJB now generates.
SchoolCriteria sCriteria = new SchoolCriteria();
CollegeCriteria cCriteria = new CollegeCriteria();
cCriteria.addTypeOfInstEqualTo( "2" );
ReportQueryByCriteria subQuery = QueryFactory.newReportQuery( College.class, cCriteria );
subQuery.setAttributes( new String[] { CollegeFields.COLLEGE_ID } );
sCriteria.addCollegeIdIn( subQuery );
QueryByCriteria sQuery = QueryFactory.newQuery( School.class, sCriteria );
sQuery.addOrderByAscending( SchoolFields.NAME_DESC );
Collection schools = broker.getCollectionByQuery( sQuery );
Its a bit of work to build the whole thing but it executes quickly and works just the way I want it to. If I run the straight SQL against what I had previously, I get the same result set. Whew!
Thanks again for everything.
Charlie
Jakob Braeuchi wrote:
hi charles,
does this mean that you write the sql manually.
one important point about o/r-mappers is, that you should have a clean object model before thinking of sql. i know that sometimes one knows the sql but not how to express it in pb-api or the like ;)
jakob
Charles N. Harvey III wrote:
Jakob,
Thanks so much for all of your pointers. I finally decided to just use report
queries. So far its just as fast and much much less hassle.
SELECT * FROM SCHOOL AS sch
INNER JOIN PROFILE AS pro
WHERE sch.college_id IN (SELECT college_id FROM COLLEGE WHERE type = '1')
Made life much easier. Thanks again for all the help. I sure did learn a lot
about OJB over the past 2 weeks.
Charlie
Jakob Braeuchi wrote:
hi charles,
Charles N. Harvey III wrote:
Ok. Great suggestion. And I tried out the query and it works great.
Here's the lousy part, there isn't just one COLLEGE table. There are 20.
COLLEGE_ADMISSIONS, COLLEGE_FRESHMEN, COLLEGE_REQUIREMENTS....
So I don't think I can make SCHOOL.college_id a FK to any of the COLLEGE.college_id's.
I have actually, a COLLEGE table. Then each of the _EXTRA tables. So I can make
SCHOOL.college_id FK to COLLEGE.college_id. And then each of the COLLEGE_EXTRA tables
subclass COLLEGE. But then I REALLY don't know how to run the queries.
why do you think that these tables need to subclass college ?
imo COLLEGE_ADMISSIONS, _REQUIREMENTS are 1:n relationships of COLLEGE, i think these are parts of the college.
jakob
Any ideas
on how to set this up would be great. I'm pretty close to going to straight SQL. D'oh.
Thanks again.
Charlie
Jakob Braeuchi wrote:
hi charles,
Charles N. Harvey III wrote:
----------------------------------------- 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
what is the PK of college ? i assume college_id. imo SCHOOL.colege_id should be a FK COLLEGE PK.
-----------------------------------------
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?
yes they do !
jakob
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]
--------------------------------------------------------------------- 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]
--------------------------------------------------------------------- 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]
