db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Danilo Tommasina <dtommas...@risksys.com>
Subject Re: GroupBy clause ignored in PersistentBroker.getCount( Query query)
Date Fri, 13 Feb 2004 07:38:11 GMT
hi jakob,

sorry my knowledge of the OJB internals is not good enough and i am a 
bit short of time for investigating further, sorry (i am just an OJB user)

I guess that this thread should be discussed further in the developer 
mailing list...

cheers
danilo


> hi danilo,
> 
> the distinct stuff works for single and multiple pk :
> 
> select count(distinct key1 || key2) this is platform dependent of course.
> 
> but the big problem i have now is the handling (translation)of the multi 
> columns  during sql generation. the current implementation assumes that 
> there's only one attribute to be translated
> 
> select count(distinct A0.pk_1 || key2)  from tab A0
> 
> the second attribute (key2) is not translated into it's column name.
> 
> jakob
> 
> Danilo Tommasina wrote:
> 
>> hi jakob,
>>
>>
>>> hi danilo,
>>>
>>> i propose to do the following :
>>>
>>> 1.) select count(*) for QueryByCriteria not using distinct
>>
>>
>>
>>  ok!
>>
>>> 2.) select count (distinct key1 || key2) for DISTINCT QueryByCriteria
>>
>>
>>
>>  ok! if you can really make it db independant, you must probably add 
>> some sort 'generateConcat' method in the Platform implementations and 
>> maybe you need some type conversion on the keys to concatenate them 
>> wich is also db dependant :(
>>
>>> 3.) special treatment for ReportQueryByCriteria because groupBy is 
>>> only relevant here.
>>
>>
>> this is true for ex. for oracle, because all selected columns must be 
>> also declared in the group by cause, but not all databases require 
>> this...
>>
>> actually i am using it with a normal QueryByCriteria ;) on 
>> schei$$base, however i am not an SQL guru and i do not know if it is a 
>> standard behaviour what Sybase does with count on a 'group by' query.
>> (Sybase delivers a relsutset of counts on the number of elements in 
>> the groups and not the number of delivered groups)
>>
>> i can see no super general solution that will work on every database...
>> maybe the best solution is just to throw an 
>> UnsupportedOperationException if the GroupBy is set (or optionally 
>> silently ignored by config in OJB.repository for backwards compatibility)
>>
>>
>> gruss aus züri
>> danilo
>>
>>>
>>> Danilo Tommasina wrote:
>>>
>>>> hi Jakob,
>>>>
>>>>
>>>>>> a ditinct clause for doing exactly what i need, however 
>>>>>> scheissbase <= 
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> this isn't the official name, isn't it ;)
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> no it isn't, but from my point of view it better describes the 
>>>> product ;)
>>>>
>>>>> i was thinking of:
>>>>> select count (distinct key1 || key2) from .... when we have 
>>>>> multiple pks.
>>>>>
>>>>> this does of course not solve the group by at all :( and of course 
>>>>> string concatenation is not the same on any platform (|| is the 
>>>>> ansi char for concatenation, but ms sql-server uses +, ms-access &,

>>>>> mysql has a function called concat...)  how is it in s...base ?
>>>>>
>>>>> imo an easy way to solve the problems would be to use an enclosing 
>>>>> count query:
>>>>>
>>>>> select count(*) from (original query)
>>>>>
>>>>> but this does not yet work in mysql !
>>>>>
>>>>>> so, sorry for the long text, but maybe sombody else has the same

>>>>>> problem and that's the (horrible) way i solved it.
>>>>>> if somebody has a better solution i would be glad to hear it, thanks
>>>>>>
>>>>
>>>> Ok, I see... FYI Sybase also uses || for string concatenation.
>>>> However it may require a type conversion for the 
>>>> string-concatenation, that is also rdms dependant.
>>>>
>>>> as you said, this doesn't solve the group by problem, however i do 
>>>> not know if all databases returns the same results with different 
>>>> combinations of group by and distinct clauses. so may be it is not 
>>>> that bad to ignore the group by clause in the getCount() method 
>>>> (however in this case a note in the doc should be done)
>>>>
>>>> so for my needs i will just adopt my special (horrible) s#!@##!base 
>>>> solution.
>>>>
>>>> thx
>>>> danilo
>>>>
>>>>>
>>>>>>
>>>>>>> hi danilo,
>>>>>>>
>>>>>>> the groupBy information is defined in the query not in the 
>>>>>>> criteria, so the copy() does not make much sense. but there's
a 
>>>>>>> problem when counting reportqueries using groupBy:
>>>>>>>
>>>>>>> Criteria crit = new Criteria();
>>>>>>> ReportQueryByCriteria q = 
>>>>>>> QueryFactory.newReportQuery(ProductGroup.class, crit);
>>>>>>> q.setColumns(new String[] { "groupName", 
>>>>>>> "sum(allArticlesInGroup.stock)",      
>>>>>>> "sum(allArticlesInGroup.price)" });
>>>>>>> q.addGroupBy("groupName");
>>>>>>>
>>>>>>> broker.getReportQueryIteratorByQuery(q);
>>>>>>> while (iter.hasNext())
>>>>>>> {
>>>>>>>     results.add(iter.next());
>>>>>>> }
>>>>>>>
>>>>>>> int count = broker.getCount(q);
>>>>>>> assertEquals(results.size(), count); <<< FAILS
>>>>>>>
>>>>>>>
>>>>>>> results in this sql:
>>>>>>>
>>>>>>> SELECT A0.KategorieName,sum(A1.Lagerbestand),sum(A1.Einzelpreis)
>>>>>>> FROM Kategorien A0
>>>>>>> LEFT OUTER JOIN BOOKS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
>>>>>>> LEFT OUTER JOIN CDS A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr
>>>>>>> LEFT OUTER JOIN Artikel A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
>>>>>>> GROUP BY A0.KategorieName
>>>>>>>
>>>>>>> ojb generates a wrong count-sql :
>>>>>>>
>>>>>>> SELECT count(*) FROM Kategorien A0
>>>>>>>
>>>>>>> or when including groupBy:
>>>>>>>
>>>>>>> SELECT count(*),A0.KategorieName as ojb_col_1 FROM Kategorien
A0 
>>>>>>> GROUP BY A0.KategorieName
>>>>>>>
>>>>>>> how should the count look like (without using a subquery) ?
>>>>>>>
>>>>>>> jakob
>>>>>>>
>>>>>>>
>>>>>>> Danilo Tommasina wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> Is there a reason why the GroupBy clause is beeing ignored
in a 
>>>>>>>> query when doing a PersistenceBorker.getCount( Query query
)?
>>>>>>>>
>>>>>>>>
>>>>>>>> In RC4 release I found this piece of code:
>>>>>>>>
>>>>>>>> class PersistenceBokerImpl
>>>>>>>>
>>>>>>>>
>>>>>>>>     public int getCount(Query query) throws 
>>>>>>>> PersistenceBrokerException
>>>>>>>>     {
>>>>>>>>
>>>>>>>> (...)
>>>>>>>>
>>>>>>>>         // build a ReportQuery based on query
>>>>>>>>         // orderby needs to be cleared
>>>>>>>>         if (query.getCriteria() != null)
>>>>>>>>         {
>>>>>>>>             reportCrit = query.getCriteria().copy(false,
false, 
>>>>>>>> false);
>>>>>>>>         }
>>>>>>>>
>>>>>>>> (...)
>>>>>>>>
>>>>>>>>     }
>>>>>>>>
>>>>>>>> The line:
>>>>>>>>
>>>>>>>> reportCrit = query.getCriteria().copy(false, false, false);
>>>>>>>>
>>>>>>>> copies the Criteria ignoring the GroupBy clause, thus causing

>>>>>>>> the count query to return a wrong number of elements.
>>>>>>>> Is there a reason for this or has it been fixed after RC4?
>>>>>>>>
>>>>>>>> thx
>>>>>>>> bye Danilo
>>>>>>>>
>>>>>>>>
>>>>>>>> ---------------------------------------------------------------------

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


Mime
View raw message