orderby column in indirection-table

[jo...@fws.gov]

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.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">
     <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

Re: orderby column in indirection-table

[Armin Waibel <ar...@apache.org>]

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.ManageableArrayList"
     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.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">
>      <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