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]
