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