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]
