Sorry for the late reply Andrew. I haven't yet tested this, but I'll try it and report back to the list.
best, Jon French Programmer ECOS Development Team [EMAIL PROTECTED] 970-226-9290 "Clute, Andrew" <[EMAIL PROTECTED]> 07/28/2005 02:14 PM Please respond to "OJB Users List" <ojb-user@db.apache.org> To "OJB Users List" <ojb-user@db.apache.org> cc Subject RE: orderby column in indirection-table FYI...There is a bug with MtoNCollectionsPrefetcher that caused certain collections indirect MtoN collections to ignore the order-by clauses. I committed a patch for that about 2 months ago, you might want to build the latest of the 1_0 release line and see if that fixes your issue. -Andrew > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 27, 2005 4:01 PM > To: OJB Users List > Subject: Re: orderby column in indirection-table > > Hi Armin: > > Your suggestion is basically what I started with except for > using the <orderby> element instead of attribute. It still > doesn't work. The Collection is not ordered and no "order by" > statement is generated (this is using 1.0.3) in the log > output from SqlGeneratorDefaultImpl. > > Here is my actual collection descriptor: > > <collection-descriptor > name="categories" > > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList" > element-class-ref="gov.doi.fis.dataobjects.WorkMeasureCategory" > indirection-table="CATEGORY_SOURCE_CROSS" > auto-update="none" > auto-delete="none" > proxy="false"> > <orderby name="CATEGORY_SOURCE_CROSS.CATEGORY_ORDER" sort="ASC"/> > <fk-pointing-to-this-class column="SOURCE_ID" /> > <fk-pointing-to-element-class column="CATEGORY_ID" /> > </collection-descriptor> > > The DDL for CATEGORY_SOURCE_CROSS is: > > CREATE TABLE FIS.CATEGORY_SOURCE_CROSS > ( > SOURCE_ID NUMBER (8) > NOT > NULL > , CATEGORY_ID NUMBER (8) > NOT > NULL > , CATEGORY_ORDER NUMBER (4) > NOT > NULL > , PRIMARY KEY (SOURCE_ID,CATEGORY_ID) > , UNIQUE (SOURCE_ID,CATEGORY_ORDER) > , FOREIGN KEY (CATEGORY_ID) REFERENCES > WORK_MEASURE_CATEGORY(CATEGORY_ID) > , FOREIGN KEY (SOURCE_ID) REFERENCES > WORK_MEASURE_SOURCE(SOURCE_ID) ); > > Thanks, > > Jon French > Programmer > ECOS Development Team > [EMAIL PROTECTED] > 970-226-9290 > > > > Armin Waibel <[EMAIL PROTECTED]> > 07/26/2005 04:47 PM > Please respond to > "OJB Users List" <ojb-user@db.apache.org> > > > To > > OJB Users List <ojb-user@db.apache.org> > cc > > > > > > Subject > Re: orderby column in indirection-table > > > > > > > Hi Jon, > > isn't it possible to do something like this: > > <collection-descriptor name="authors" > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList" > element-class-ref="package.name.Author" > indirection-table="BOOK_AUTHOR_CROSS" > auto-update="none" > auto-delete="none" > proxy="true"> > > <orderby name="BOOK_AUTHOR_CROSS.AUTHOR_ORDER" sort="ASC"/> > > <fk-pointing-to-this-class column="BOOK_ID" /> > <fk-pointing-to-element-class column="AUTHOR_ID" /> > </collection-descriptor> > > this should add an order by using the unchanged cloumn name (Don't > forget to remove the "old" orderby attribute). > > regards, > Armin > > [EMAIL PROTECTED] wrote: > > Thanks for your reply Armin: > > > > In the test-case you gave me, the values of both <orderby> "name" > > attributes ("name" and "MOVIE_ID_INT") are valid identifiers for > > <field-descriptor> on the M2NTest$Actor class. The first is > a property > > name of the object and the second is a table column of a property. > > > > This case is a bit different than what I need because the > column for > which > > I would like to orderby is on the indirection-table, not on the > > element-class table. > > > > Your statement that my indirection table isn't a "pure" indirection > table > > is true. I'm definitely stretching the definition by adding an > additional > > attribute. I would still like to avoid mapping a > class-descriptor for > the > > m:n association if possible. > > > > Right now, I have to use release 1.0.3 and moving the > orderby attribute > to > > an <orderby> element didn't change the generated sql. > > > > > >>>How is the AUTHOR_ORDER column populated? > > > > > > In my case, the m:n association is relatively static and will be > populated > > by hand external to OJB. I'll never have a need to add an > AUTHOR to a > BOOK > > and thus don't need to worry about insertions into the > indirection-table. > > > > I'll look into the 1.0.3 source code further. > > > > Best, > > > > Jon French > > Programmer > > ECOS Development Team > > [EMAIL PROTECTED] > > 970-226-9290 > > > > > > > > Armin Waibel <[EMAIL PROTECTED]> > > 07/26/2005 12:32 PM > > Please respond to > > "OJB Users List" <ojb-user@db.apache.org> > > > > > > To > > > > OJB Users List <ojb-user@db.apache.org> > > cc > > > > > > > > > > > > Subject > > Re: orderby column in indirection-table > > > > > > > > > > > > > > Hi Jon, > > > > In your case the indirection table isn't a real indirection table, > > because you store additional information in column > AUTHOR_ORDER. How is > > the AUTHOR_ORDER column populated? > > Think OJB will ignore this column when handling the m:n > relation between > > > Book and Author - or I'm wrong? > > > > Anyway you should use the new 'orderby' element to specify > the order by > > fields in your reference, the 'orderby-attribute' is deprecated now. > > > > An example: > > > > <collection-descriptor name="actors" > > > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList" > > element-class-ref="org.apache.ojb.broker.M2NTest$Actor" > > auto-retrieve="false" > > auto-update="false" > > auto-delete="false" > > indirection-table="M2N_TEST_ROLE" > > > > > <orderby name="name" sort="ASC"/> > > <orderby name="MOVIE_ID_INT" sort="DESC"/> > > > > > > <fk-pointing-to-this-class column="MOVIE_ID_INT"/> > > <fk-pointing-to-this-class column="MOVIE_ID2_INT"/> > > <fk-pointing-to-this-class column="MOVIE_ID_STR"/> > > <fk-pointing-to-element-class column="ACTOR_ID"/> > > <fk-pointing-to-element-class column="ACTOR_ID2"/> > > </collection-descriptor> > > > > The first orderby point to the 'name' field in Movie class. > The second > > one use the column name of a column in indirection table. > If OJB doesn't > > > find a field name it use the specified String unchanged > (MOVIE_ID_INT). > > The generated sql look like this: > > > > SELECT A0.OBJ_ID,A0.OBJ_ID2,A0.NAME,MOVIE_ID_INT as ojb_col_4 > > FROM M2N_TEST_ACTOR A0,M2N_TEST_ROLE > > WHERE ((((M2N_TEST_ROLE.MOVIE_ID_INT = '1') > > AND M2N_TEST_ROLE.MOVIE_ID2_INT = '2') > > AND M2N_TEST_ROLE.MOVIE_ID_STR = > 'doTestAddNewEntries_1122401682890') > > AND M2N_TEST_ROLE.ACTOR_ID = A0.OBJ_ID) > > AND M2N_TEST_ROLE.ACTOR_ID2 = A0.OBJ_ID2 > > ORDER BY 4 DESC, 3 > > > > Use latest OJB from CVS (OJB_1_0_RELEASE branch) to run this test. > > > > regards, > > Armin > > > > [EMAIL PROTECTED] wrote: > > > >>OJB-users: > >> > >>I would like to order a <collection-descriptor> based on > the value of a > >>column in an indirection table. For instance, if a BOOK has an m:n > >>relation to AUTHORS specified via the indirection > BOOK_AUTHOR_CROSS, and > > > > > > >>BOOK_AUTHOR_CROSS has columns: > >> > >>BOOK_ID, > >>AUTHOR_ID, > >>AUTHOR_ORDER > >> > >>I would like the Collection of AUTHORS for a given book to > be ordered by > > > > > > >>the natural ordering of column AUTHOR_ORDER. > >> > >>I've found previous list posts that recommended that I do > this via the > >>orderby attribute in the collection-descriptor like this: > >> > >> <collection-descriptor > >> name="authors" > >> > >> > > > > > collection-class="org.apache.ojb.broker.util.collections.Manag > eableArrayList" > > > >> element-class-ref="package.name.Author" > >> indirection-table="BOOK_AUTHOR_CROSS" > >> auto-update="none" > >> auto-delete="none" > >> orderby="BOOK_AUTHOR_CROSS.AUTHOR_ORDER" > >> proxy="true"> > >> <fk-pointing-to-this-class column="BOOK_ID" /> > >> <fk-pointing-to-element-class column="AUTHOR_ID" /> > >> </collection-descriptor> > >> > >>However, the SQL generated (from SQLGeneratorDefaultImpl) for the > >>Collection look-up does not contain an "order by" clause, > the collection > > > > > > >>is definitely not ordered properly, and changing the > orderby attribute > > > > to > > > >>a bogus column name does not throw an error (ie. > >>BOOK_AUTHOR_CROSS.AUTHOR_ORDER_BOGUS) as I would expect it > to do if it > > > > was > > > >>being used. > >> > >>I understand from the repository.dtd documentation that the orderby > >>attribute is supposed to reference a <field-descriptor> on the > >>element-class, but I thought OBJ used to support this > alternate form of > >>ordering for non-decomposed mappings. > >> > >>Thanks, > >> > >>Jon French > >>Programmer > >>ECOS Development Team > >>[EMAIL PROTECTED] > >>970-226-9290 > >> > >> > >> > > > > > > > --------------------------------------------------------------------- > > 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]