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,

Janssen, Roger wrote:
> 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.
>

This behavior is intended by OJB. OJB adds all "unknown fields/columns" 
to the query statement. See SqlQueryStatement#ensureColumnsOrderBy.

If you change this method in the same way like #ensureColumnsGroupBy 
works, the fields won't be added and you won't get duplicate items.

This works with e.g. mysql but doesn't work for e.g. maxDB, oracle - 
they always expect an output column in the order-by clause.
So, I don't know how to solve this issue? Should we make this 
configurable e.g. Query.addMissingOrderByColumns(boolean enable)?


> 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.

ok I will try to setup some tests too.

regards,
Armin

> 
> 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]
> 
>