You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Janssen, Roger" <ro...@ibanx.nl> on 2008/01/31 09:39:24 UTC
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,
When you have two object classes, Permit and AbstractAttribute and
Permit holds a collection of AbstractAttributes (using a collection
descriptor for the abstractattribute collection on the Permit in the
mapping file), we can create a query like below:
Criteria crit1 = new
Criteria().addEqualTo("allAbstractAttributes.name", "aaTest");
UserAlias ua = new UserAlias("myAlias");
crit1.setAlias(ua);
Criteria crit2 = new
Criteria().addLike("allAbstractAttributes.value", "*");
crit2.setAlias(ua);
crit1.addAndCriteria(crit2);
QueryByCriteria query = QueryFactory.newQuery(Permit.class,
crit1, true);
query.addOrderBy("myAlias.value", true);
Collection c = pb.getCollectionByQuery(query);
In this query I use a useralias to reference a specific join in my
orderby clause. However the generated SQL looks something like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY myAlias.value
So we observe that the defined useralias on the join is not processed
within the orderby clause, and I get a wrong SQL statement throwing an
SQL exception. I would have expected a query like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY A1.VALUE
Again, I did some OJB code hacking. I came up with a patch, but.... I
have the feeling it might be done better/smarter/more efficient and
maybe even implemented in some other location in the code.... But the
SQL generation code was not quite that transparent regarding this
aspect, so just analyse my patch and see what you do with it.
In the class SqlSelectStatement, in the method protected String
buildSqlString(), there is the following code:
...
...
groupByFields = query.getGroupBy();
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
In here the groupByFields and the orderByFields contain the lists of
attributes like the user specified them, so with the useraliases. These
need to be replaced with the generated alias before the
ensurecolumn-methods are called. So I implemented a method
replaceUserAlias doing exactly that and I call this method before the
call to the ensurecolumns-methods.
The code then becomes:
...
...
groupByFields = query.getGroupBy();
// start - iBanx patch
replaceUserAlias(groupByFields, whereCrit);
// end - iBanx patch
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
// start - iBanx patch
replaceUserAlias(orderByFields, whereCrit);
// end - iBanx patch
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
The method implementation is:
// start - iBanx patch
/**
* Replaces any useralias reference in the fieldlist with the
corresponding generated alias of the join releated to the
* whereCrit clause.
*
* @param fields
* @param whereCrit
*/
private void replaceUserAlias(List fields, Criteria whereCrit)
{
// defensive programming preventing NPE's
if((getRoot()!=null) && (whereCrit!=null) &&
(whereCrit.getUserAlias()!=null) && (fields!=null))
{
// continue when we have a join
// -- test it like this because the iterateJoins() method
throes NPE when joins equals null
if(getRoot().joins != null)
{
// iterate over all the joins to check for useraliases
Iterator theJoins = getRoot().iterateJoins();
if(theJoins!=null)
{
while(theJoins.hasNext())
{
Join j = (Join)theJoins.next();
if(j.right!=null)
{
// iterate over all the fields in the list
that might contain useraliases
for(int i = 0;i < fields.size();i++)
{
FieldHelper fh =
(FieldHelper)fields.get(i);
// if the field has a useralias....
replecae it with the generated alias
if(fh.name.startsWith(whereCrit.getUserAlias().getName() + "."))
{
// generate new fieldname with the
generated alias
String fhname = j.right.alias +
fh.name.substring(whereCrit.getUserAlias().getName().length());
// remove the 'old' field from the
list
fields.remove(i);
// instantiate a new fieldhelper
with the new proper name
FieldHelper nfh = new
FieldHelper(fhname, fh.isAscending);
// insert it into the list at the
same location
fields.add(i, nfh);
}
}
}
}
}
}
}
}
// end - iBanx patch
I have tested this patch for the given orderby-scenario, not with a
useralias in the groupby clause. This patch seems to work for me in this
case, but like I said... Some of you guys probably have a better fix for
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
Hi Roger,
Janssen, Roger wrote:
> 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.
You can get duplicate objects back too if the order-by clause contains
fields which are not part of the output columns (class fields). It's a
general behavior of OJB.
For example, if I modify #ensureColumnsOrderBy and specify a order-by
field/column which is not part of the output columns we get:
1.)
SELECT DISTINCT A0.ID_,A0.TITLE_,A0.VERSION_
FROM SAMPLE_BOOK A0
INNER JOIN SAMPLE_REVIEW A1 ON A0.ID_=A1.FK_BOOK
WHERE (A0.ID_ < A1.ID_) AND
A0.TITLE_ LIKE '%_testOrderBy_2_1204304719937_%'
ORDER BY A1.ID_ DESC
This doesn't work with maxDB and oracle (I suppose other DB too). As
workaround OJB always adds the missing columns as "hidden" output
columns. With the unmodified #ensureColumnsOrderBy method OJB will generate:
2.)
SELECT DISTINCT A0.ID_,A0.TITLE_,A0.VERSION_,A1.ID_ as ojb_col_4
FROM SAMPLE_BOOK A0
INNER JOIN SAMPLE_REVIEW A1 ON A0.ID_=A1.FK_BOOK
WHERE (A0.ID_ < A1.ID_) AND
A0.TITLE_ LIKE '%_testOrderBy_2_1204305513406_%'
ORDER BY 4 DESC
I don't know if the order-by clause in the 1. example makes sense, in
the 2. example the returned result set makes sense (with ordered A1.ID_
column and duplicated A0.ID_,A0.TITLE_,A0.VERSION_ columns for each
match) but the result returned by OJB ignore the ojb_col_4 and only
returns A0.ID_,A0.TITLE_,A0.VERSION_.
>
> 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..
If we make it configurable you can choice which behavior of OJB you
prefer (1. doesn't work with all DB, or 2. works for all DB but return
unexpected duplicates).
regards,
Armin
>
> Roger Janssen
>
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> 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:arminw@apache.org]
>> 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: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>
>>>>>
>>>> --------------------------------------------------------------------
>>>> - To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
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:arminw@apache.org]
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:arminw@apache.org]
> 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: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>> --------------------------------------------------------------------
>>> - To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
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:arminw@apache.org]
> 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: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
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:arminw@apache.org]
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: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
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: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
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: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by Armin Waibel <ar...@apache.org>.
Hi Roger,
Janssen, Roger wrote:
>
> Hi,
>
> When you have two object classes, Permit and AbstractAttribute and
> Permit holds a collection of AbstractAttributes (using a collection
> descriptor for the abstractattribute collection on the Permit in the
> mapping file), we can create a query like below:
>
>
> Criteria crit1 = new
> Criteria().addEqualTo("allAbstractAttributes.name", "aaTest");
> UserAlias ua = new UserAlias("myAlias");
> crit1.setAlias(ua);
> Criteria crit2 = new
> Criteria().addLike("allAbstractAttributes.value", "*");
> crit2.setAlias(ua);
> crit1.addAndCriteria(crit2);
>
> QueryByCriteria query = QueryFactory.newQuery(Permit.class,
> crit1, true);
>
> query.addOrderBy("myAlias.value", true);
>
> Collection c = pb.getCollectionByQuery(query);
>
You are right, this doesn't work with 1.0.5rc1 (with 1.0.4 it doesn't
work too). Is this the correct code? AFAIK this query work without an
alias (my local test show the same generated sql with and without alias):
Criteria crit1 = new Criteria().addEqualTo("allAbstractAttributes.name",
"aaTest");
Criteria crit2 = new Criteria().addLike("allAbstractAttributes.value",
"*").addAndCriteria(crit2);
QueryByCriteria query = QueryFactory.newQuery(Permit.class,crit1, true);
query.addOrderBy("allAbstractAttributes.value", true);
I setup some new query tests with order by clause but I can't think out
a useful test using an alias in the order by clause. Fixing this "bug"
won't be easy (you already notice that ;-)), so I want be sure that it
is important to support alias-names in oder by and having clause.
regards,
Armin
>
> In this query I use a useralias to reference a specific join in my
> orderby clause. However the generated SQL looks something like:
>
> SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
> WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
> ORDER BY myAlias.value
>
> So we observe that the defined useralias on the join is not processed
> within the orderby clause, and I get a wrong SQL statement throwing an
> SQL exception. I would have expected a query like:
>
> SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
> WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
> ORDER BY A1.VALUE
>
> Again, I did some OJB code hacking. I came up with a patch, but.... I
> have the feeling it might be done better/smarter/more efficient and
> maybe even implemented in some other location in the code.... But the
> SQL generation code was not quite that transparent regarding this
> aspect, so just analyse my patch and see what you do with it.
>
> In the class SqlSelectStatement, in the method protected String
> buildSqlString(), there is the following code:
>
> ...
> ...
> groupByFields = query.getGroupBy();
> ensureColumnsGroupBy(groupByFields, columnList);
>
> orderByFields = query.getOrderBy();
> columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
> ...
> ...
>
> In here the groupByFields and the orderByFields contain the lists of
> attributes like the user specified them, so with the useraliases. These
> need to be replaced with the generated alias before the
> ensurecolumn-methods are called. So I implemented a method
> replaceUserAlias doing exactly that and I call this method before the
> call to the ensurecolumns-methods.
>
> The code then becomes:
>
> ...
> ...
> groupByFields = query.getGroupBy();
> // start - iBanx patch
> replaceUserAlias(groupByFields, whereCrit);
> // end - iBanx patch
> ensureColumnsGroupBy(groupByFields, columnList);
>
> orderByFields = query.getOrderBy();
> // start - iBanx patch
> replaceUserAlias(orderByFields, whereCrit);
> // end - iBanx patch
> columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
>
> ...
> ...
>
> The method implementation is:
>
> // start - iBanx patch
> /**
> * Replaces any useralias reference in the fieldlist with the
> corresponding generated alias of the join releated to the
> * whereCrit clause.
> *
> * @param fields
> * @param whereCrit
> */
> private void replaceUserAlias(List fields, Criteria whereCrit)
> {
> // defensive programming preventing NPE's
> if((getRoot()!=null) && (whereCrit!=null) &&
> (whereCrit.getUserAlias()!=null) && (fields!=null))
> {
> // continue when we have a join
> // -- test it like this because the iterateJoins() method
> throes NPE when joins equals null
> if(getRoot().joins != null)
> {
> // iterate over all the joins to check for useraliases
> Iterator theJoins = getRoot().iterateJoins();
> if(theJoins!=null)
> {
> while(theJoins.hasNext())
> {
> Join j = (Join)theJoins.next();
> if(j.right!=null)
> {
> // iterate over all the fields in the list
> that might contain useraliases
> for(int i = 0;i < fields.size();i++)
> {
> FieldHelper fh =
> (FieldHelper)fields.get(i);
> // if the field has a useralias....
> replecae it with the generated alias
>
> if(fh.name.startsWith(whereCrit.getUserAlias().getName() + "."))
> {
> // generate new fieldname with the
> generated alias
> String fhname = j.right.alias +
> fh.name.substring(whereCrit.getUserAlias().getName().length());
> // remove the 'old' field from the
> list
> fields.remove(i);
> // instantiate a new fieldhelper
> with the new proper name
> FieldHelper nfh = new
> FieldHelper(fhname, fh.isAscending);
> // insert it into the list at the
> same location
> fields.add(i, nfh);
> }
> }
> }
> }
> }
> }
> }
> }
> // end - iBanx patch
>
> I have tested this patch for the given orderby-scenario, not with a
> useralias in the groupby clause. This patch seems to work for me in this
> case, but like I said... Some of you guys probably have a better fix for
> 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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi,
In a response to my own patch I send, it may have a bug in it....
It replaces the useralias in the orderby so 'myAlias.value' becomes
'A1.value' and then further standard processing adds 'A1.value as
ojb_col_xx' to the select-columnlist in the query. Bu as you notice the
column name 'value' is lowercase. I suspect that the reference to the
java property 'value' is not mapped on the column 'VALUE', but the
reference to the java property is just left in the clause unchanged, and
that I expect has something to do with the use of the useralias. In my
testcase, there is no problem because the java property and the mapped
table column are the same, both are 'value', so the SQL statement is
correct. But when the java propertyname and the tablename are not the
same, an illegal statement will be created.
So I do not know if I can fix this in my patch...
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
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
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi again,
The problem is real but the patch I described (see original message) is
flawed. So forget that patch.
I think the solution direction is probably something like:
- pas the useralias to the ensurecolumns methods (they should pass it
on,
but now they do not have it and they pass null, so we lost the notion
of the
useralias processing the orderby columns)
- following the flow of execution, I believe the inner class
SingleAttributeInfo in class
SqlQueryStatement should have a property to store the useralias (so
the constructor should
receive this as an argument)
- again following the flow of execution, I believe somewhere in methods
like the method
protected void appendColumn(SingleAttributeInfo anAttrInfo, boolean
translate, StringBuffer buf)
some logic shopuld be added using the useralias and the tablealias to
generate the proper SQL
But like I said in earlier mail... the code is not that transparent and
I have not yet figured out how to solve this issue. Hope you guys can.
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Thursday, January 31, 2008 9:39 AM
To: OJB Users List
Subject: 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,
When you have two object classes, Permit and AbstractAttribute and
Permit holds a collection of AbstractAttributes (using a collection
descriptor for the abstractattribute collection on the Permit in the
mapping file), we can create a query like below:
Criteria crit1 = new
Criteria().addEqualTo("allAbstractAttributes.name", "aaTest");
UserAlias ua = new UserAlias("myAlias");
crit1.setAlias(ua);
Criteria crit2 = new
Criteria().addLike("allAbstractAttributes.value", "*");
crit2.setAlias(ua);
crit1.addAndCriteria(crit2);
QueryByCriteria query = QueryFactory.newQuery(Permit.class,
crit1, true);
query.addOrderBy("myAlias.value", true);
Collection c = pb.getCollectionByQuery(query);
In this query I use a useralias to reference a specific join in my
orderby clause. However the generated SQL looks something like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY myAlias.value
So we observe that the defined useralias on the join is not processed
within the orderby clause, and I get a wrong SQL statement throwing an
SQL exception. I would have expected a query like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY A1.VALUE
Again, I did some OJB code hacking. I came up with a patch, but.... I
have the feeling it might be done better/smarter/more efficient and
maybe even implemented in some other location in the code.... But the
SQL generation code was not quite that transparent regarding this
aspect, so just analyse my patch and see what you do with it.
In the class SqlSelectStatement, in the method protected String
buildSqlString(), there is the following code:
...
...
groupByFields = query.getGroupBy();
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
In here the groupByFields and the orderByFields contain the lists of
attributes like the user specified them, so with the useraliases. These
need to be replaced with the generated alias before the
ensurecolumn-methods are called. So I implemented a method
replaceUserAlias doing exactly that and I call this method before the
call to the ensurecolumns-methods.
The code then becomes:
...
...
groupByFields = query.getGroupBy();
// start - iBanx patch
replaceUserAlias(groupByFields, whereCrit);
// end - iBanx patch
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
// start - iBanx patch
replaceUserAlias(orderByFields, whereCrit);
// end - iBanx patch
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
The method implementation is:
// start - iBanx patch
/**
* Replaces any useralias reference in the fieldlist with the
corresponding generated alias of the join releated to the
* whereCrit clause.
*
* @param fields
* @param whereCrit
*/
private void replaceUserAlias(List fields, Criteria whereCrit)
{
// defensive programming preventing NPE's
if((getRoot()!=null) && (whereCrit!=null) &&
(whereCrit.getUserAlias()!=null) && (fields!=null))
{
// continue when we have a join
// -- test it like this because the iterateJoins() method
throes NPE when joins equals null
if(getRoot().joins != null)
{
// iterate over all the joins to check for useraliases
Iterator theJoins = getRoot().iterateJoins();
if(theJoins!=null)
{
while(theJoins.hasNext())
{
Join j = (Join)theJoins.next();
if(j.right!=null)
{
// iterate over all the fields in the list
that might contain useraliases
for(int i = 0;i < fields.size();i++)
{
FieldHelper fh =
(FieldHelper)fields.get(i);
// if the field has a useralias....
replecae it with the generated alias
if(fh.name.startsWith(whereCrit.getUserAlias().getName() + "."))
{
// generate new fieldname with the
generated alias
String fhname = j.right.alias +
fh.name.substring(whereCrit.getUserAlias().getName().length());
// remove the 'old' field from the
list
fields.remove(i);
// instantiate a new fieldhelper
with the new proper name
FieldHelper nfh = new
FieldHelper(fhname, fh.isAscending);
// insert it into the list at the
same location
fields.add(i, nfh);
}
}
}
}
}
}
}
}
// end - iBanx patch
I have tested this patch for the given orderby-scenario, not with a
useralias in the groupby clause. This patch seems to work for me in this
case, but like I said... Some of you guys probably have a better fix for
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org