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.ManageableArrayList"
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
[EMAIL PROTECTED]
970-226-9290
Armin Waibel <[EMAIL PROTECTED]>
07/26/2005 04:47 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,
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]
