RE: orderby column in indirection-table

[jo...@fws.gov]

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
jon_french@fws.gov
970-226-9290



"Clute, Andrew" <An...@etech.ohio.gov> 
07/28/2005 02:14 PM
Please respond to
"OJB Users List" <oj...@db.apache.org>


To

"OJB Users List" <oj...@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: jon_french@fws.gov [mailto:jon_french@fws.gov] 
> 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
> jon_french@fws.gov
> 970-226-9290
> 
> 
> 
> Armin Waibel <ar...@apache.org>
> 07/26/2005 04:47 PM
> Please respond to
> "OJB Users List" <oj...@db.apache.org>
> 
> 
> To
> 
> OJB Users List <oj...@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
> 
> jon_french@fws.gov 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
> > jon_french@fws.gov
> > 970-226-9290
> > 
> > 
> > 
> > Armin Waibel <ar...@apache.org> 
> > 07/26/2005 12:32 PM
> > Please respond to
> > "OJB Users List" <oj...@db.apache.org>
> > 
> > 
> > To
> > 
> > OJB Users List <oj...@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
> > 
> > jon_french@fws.gov 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
> >>jon_french@fws.gov
> >>970-226-9290
> >>
> >>
> >>
> > 
> > 
> > 
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> > 
> > 
> > 
> > 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 
> 

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