RE: orderby column in indirection-table
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" To "OJB Users List" 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 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: > > 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"> > > > > > > The DDL for CATEGORY_SOURCE_CROSS is: > > CREATE TABLE FIS.CATEGORY_SOURCE_CROSS > ( > SOURCE_IDNUMBER (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" > > > To > > OJB Users List > cc > > > > > > Subject > Re: orderby column in indirection-table > > > > > > > Hi Jon, > > isn't it possible to do something like this: > > 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"> > > > > > > > > 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 "name" > > attributes ("name" and "MOVIE_ID_INT") are valid identifiers for > > 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 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 > indire
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 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: > > 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"> > > > > > > The DDL for CATEGORY_SOURCE_CROSS is: > > CREATE TABLE FIS.CATEGORY_SOURCE_CROSS > ( > SOURCE_IDNUMBER (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" > > > To > > OJB Users List > cc > > > > > > Subject > Re: orderby column in indirection-table > > > > > > > Hi Jon, > > isn't it possible to do something like this: > > 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"> > > > > > > > > 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 "name" > > attributes ("name" and "MOVIE_ID_INT") are valid identifiers for > > 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 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
Re: orderby column in indirection-table
Hi Armin: Your suggestion is basically what I started with except for using the 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: The DDL for CATEGORY_SOURCE_CROSS is: CREATE TABLE FIS.CATEGORY_SOURCE_CROSS ( SOURCE_IDNUMBER (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" To OJB Users List cc Subject Re: orderby column in indirection-table Hi Jon, isn't it possible to do something like this: 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 "name" > attributes ("name" and "MOVIE_ID_INT") are valid identifiers for > 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 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" > > > To > > OJB Users List > 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-class="org.apache.ojb.broker.util.collections.ManageableArrayList" > element-class-ref="org.apache.ojb.broker.M2NTest$Actor" > auto-retrieve="false" > auto-update="false" > auto-delete="false" > indirection-table="M2N_TEST_ROLE" > > > > > > > > > > > > > > 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 fro
Re: orderby column in indirection-table
Hi Jon, isn't it possible to do something like this: 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 "name" attributes ("name" and "MOVIE_ID_INT") are valid identifiers for 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 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" To OJB Users List 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: 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 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-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 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"> 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 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]
Re: orderby column in indirection-table
Thanks for your reply Armin: In the test-case you gave me, the values of both "name" attributes ("name" and "MOVIE_ID_INT") are valid identifiers for 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 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" To OJB Users List 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: 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 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: > > name="authors" > > collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" > 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"> > > > > > 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 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]
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: 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 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-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 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"> 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 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]