Hi Armin, Just to make it absolutely clear so we talk about the same thing;
When I execute a reportquery, I expect to get records back, and if I get duplicate records back because of the joins I created, well that is fine. But when I execute a 'normal' query, and OJB returns instantiated objects, I would like to get a list without duplicated records. If this just is not possible, so be it. It is always nice to have something configurable :) ... But adding a new method to invoke different behaviour on different databases, I don't know if that's what we all want... I am not eager to write database dependent code... So if it is not unavoidable to prevent duplicate records... I can live with that.. Roger Janssen -----Original Message----- From: Armin Waibel [mailto:[EMAIL PROTECTED] Sent: Friday, February 29, 2008 4:56 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, 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] > > --------------------------------------------------------------------- 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]
