Hi Jon,
isn't it possible to do something 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"
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" <[email protected]>
To
OJB Users List <[email protected]>
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.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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
