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
[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.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
[EMAIL PROTECTED]
970-226-9290
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
