Hi Armin,
Tested what you described and the ordering seems to work in our setup as
well.
What does not work however is that I get duplicate items returned. For
every match in the right-handside of the 1:N relation, I get the same
object returned. Even if I execute a distinct query.
Also the setPathOuterJoin functionality does not work. Inner joins keep
being generated. After this latest info from you I tried the same with
the setPathOuterJoin :
query.setPathOuterJoin("myAlias");
And
query.setPathOuterJoin("myAlias.collectionAttr");
But no luck.
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Armin Waibel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 27, 2008 2:51 PM
To: OJB Users List
Subject: Re: BUGREPORT : OJB 1.0.5rc1 : UserAliases in OrderBy clauses
are not replaced by the generated aliases for the joins resulting in
illegal SQL statements
Hi Roger,
I start work on OJB-137 (your issue) and noticed while "playing" with
order-by tests that the following notations seems to work (class Book
has a 1:n relation to class Review):
Criteria c1 = new Criteria()
.addEqualTo("reviews.summary", "3_review" + name);
c1.setAlias("alias_1"); Criteria c2 = new Criteria()
.addEqualTo("reviews.summary", "4_review" + name);
c2.setAlias("alias_2"); c1.addAndCriteria(c2); ReportQueryByCriteria q =
QueryFactory.newReportQuery(Sample.Book.class,
c1, true);
q.setAttributes(new String[]{"id", "title","reviews.id"});
q.addOrderByDescending("alias_2.reviews.id");
If I prefix the path expression with the user alias it seems to work
("alias_2.reviews.id" instead of "alias_2.id"). Did you tried this too?
regards,
Armin
Armin Waibel wrote:
> Hi Roger,
>
> I create two new "user-alias" related bug reports:
>
> http://issues.apache.org/jira/browse/OJB-137
>
> https://issues.apache.org/jira/browse/OJB-139
>
> OJB-137 reflects your issue. Locally I fixed OJB-139 (easier to fix
> then
> 137) and start work on OJB-137 (seems more complex to fix).
>
> regards,
> Armin
>
> Armin Waibel wrote:
>> Hi Roger,
>>
>> now I get your point (I'm a bit slow on the uptake ;-)). I start
>> writing many new "order by" tests to isolate the problem and to make
>> sure that changes don't have unrequested side-effects.
>>
>> > itself was not stable enough for us). In fact, I helped
>> implementing > this feature in OJB a long time ago, and I believe
>> the documentation > on > the OJB site is the documentation I once
>> sent to you guys.
>>
>> You are right, I found your example in the query documentation. But I
>> can't find a test in the OJB test-suite. I think this is the reason
>> why this feature got lost between 1.0.rc6 and now (the criteria/query
>> stuff was reworked/improved since 1.0rc6).
>>
>> My new tests show another bug when using a user alias on a 1:n
>> relation with table-per-subclass inheritance - but this is another
>> story. After finish test writing, I will do my best to find a patch
>> for your problem and keep you up-to-date.
>>
>> regards,
>> Armin
>>
>> Janssen, Roger wrote:
>>> Hi,
>>>
>>> The management summary answer to your question "is it important to
>>> support alias-names in order by and having clause" is : Yes, it is
>>> really really really important!!!
>>>
>>> So now for some background information explaining our situation.
>>>
>>> We implemented a concept to support non-modelled (abstract)
attributes.
>>> We need to support many different complex forms and datasets for our
>>> customers. The deviation between customers is huge, so modifying our
>>> domain model (and thus our pojos and thus out database tables) for
>>> every customer implementation is not an option. We need to have a
>>> stable core domain model.
>>>
>>> So all our objects in our domain model have a collection property
>>> that is capable of holding a collection of abstract-attribute
>>> instances, it's a standard 1:N relation which OJB supports. Abstract
>>> attribute classes are classes implementing name-value tuples, the
>>> name of the attribute, and the value of the attribute. So adding new
>>> attributes to specific objectclass instances implies we just have to
>>> add abstract attribute instances to the collection and OJB will
>>> store them in the apropriate table in the database. We do not have
>>> to modify our domain model by adding new java properties to the
>>> affected classes and adding new columns to the affected tables.
>>>
>>> So querying for values of object properties, represented by abstract
>>> attributes, we need to create clauses combining the name/value
>>> properties of the abstract attribute instances.
>>>
>>> So an example query with two selection criteria based on abstract
>>> attributes of an object could be:
>>>
>>> SELECT * FROM PERMIT AS A0 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS
>>> A1 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS A2 WHERE A0.ID=A1.ID AND
>>> A0.ID=A2.ID AND
>>> (A1.NAME='<name of attribute 1> AND A1.VALUE='<value of
>>> attribute
>>> 1>') AND
>>> (A2.NAME='<name of attribute 2> AND A2.VALUE='<value of
>>> attribute
>>> 2>');
>>>
>>> Since we need to work with bounded name/value pairs, we need to bind
>>> them together using a alias, in this example the aliases are A1 and
A2.
>>>
>>> So if we want to order the resultset on such an abstract attribute
>>> value the query would look like:
>>>
>>> SELECT * FROM PERMIT AS A0 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS
>>> A1 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS A2 WHERE A0.ID=A1.ID AND
>>> A0.ID=A2.ID AND
>>> (A1.NAME='<name of attribute 1> AND A1.VALUE='<value of
>>> attribute
>>> 1>') AND
>>> (A2.NAME='<name of attribute 2> AND A2.VALUE='<value of
>>> attribute
>>> 2>')
>>> ORDER BY A2.VALUE;
>>>
>>> We need to use the alias to link the orderby to the proper join, of
>>> which there are two, to the same join table (the abstract attribute
>>> table)!
>>>
>>> This is rather common functionality, currently supported in all our
>>> applications, supported by OJB 1.0rc6 that we use (the 1.0 release
>>> itself was not stable enough for us). In fact, I helped implementing
>>> this feature in OJB a long time ago, and I believe the documentation
>>> on the OJB site is the documentation I once sent to you guys.
>>>
>>> The upcoming 1.0.5 release has some features that we have been
>>> waiting for, for several years. We have been able to postpone
>>> improvements in our applications for this long, but we cannot do
>>> this any longer. So we really need these new features 1.0.5
>>> implements, but we need the user-alias in the orderby to work as
>>> well, otherwise we will loose a lot of functionality and our
customers will not accept that.
>>>
>>> Some of the required features are the native limit-clause
>>> implementations, the reference descriptor implementation without
>>> using foreign-keys (I requested this feature for more then a year
>>> ago myself), etc., etc..
>>> I hope you now have an understanding of how we use this feature, and
>>> that we cannot afford to loose it.
>>>
>>> Greetings,
>>>
>>> Roger Janssen
>>> iBanx
>>> ********************************************************************
>>> *****
>>>
>>> The information contained in this communication is confidential and
>>> is intended solely for the use of the individual or entity to whom
>>> it is addressed.You should not copy, disclose or distribute this
>>> communication without the authority of iBanx bv. iBanx bv is neither
>>> liable for the proper and complete transmission of the information
>>> has been maintained nor that the communication is free of viruses,
>>> interceptions or interference.
>>> If you are not the intended recipient of this communication please
>>> return the communication to the sender and delete and destroy all
>>> copies.
>>>
>>>
>>>
>>> --------------------------------------------------------------------
>>> - 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]
>>
>>
>
> ---------------------------------------------------------------------
> 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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
