You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@juddi.apache.org by Kurt T Stam <ku...@gmail.com> on 2009/08/14 06:35:26 UTC

[DISCUSSION] Finders and sort by - Invalid SQL

Hi guys,

When we do searches for BusinessEntities, BindingTemplates, Services and 
TModels we sort by name. However the name is not in the
business_entity table, but rather in the business_name, because multiple 
business names can be given (for different locales). In the code we
select the business entity and then try to sort by name. Some dbs think 
this is ok, but in fact most of them do not like it (including Derby). 
You get an error which is explained pretty well in this article: 
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

So we can fix this by not doing a "select distinct" but rather doing a 
"group by". However it begs the question what it means to
sort by name.. I guess you'd only want to sort within one locale, but we 
may not always have a name in each locale. So should we simply sort
by business_entity key instead?

Here are some examples:
./src/main/java/org/apache/juddi/query/FetchBindingTemplatesQuery.java:    
private static void appendSortTables(DynamicQuery qry)
./src/main/java/org/apache/juddi/query/FetchBusinessEntitiesQuery.java:    
private static void appendSortTables(DynamicQuery qry)
./src/main/java/org/apache/juddi/query/FetchBusinessServicesQuery.java:    
private static void appendSortTables(DynamicQuery qry) {
./src/main/java/org/apache/juddi/query/FetchTModelsQuery.java:    
private static void appendSortTables(DynamicQuery qry) {

--Kurt


Re: [DISCUSSION] Finders and sort by - Invalid SQL

Posted by Kurt T Stam <ku...@gmail.com>.
OK I let's try to separate the issues here:

1. We need to change distinct to "group by".
2. The sorting is still governed by the FindQualifiers, so i think we're 
good here.
3. If i.e. you search by name, and do not specify a locale, you now get 
multiple names for each business. I think it's going to be hard to sort 
by name, where the name is the primary (the record inserted first). I 
guess we should build a little db and come up with the desired SQL..

Are we on the same page so far?

-K

Jeff Faath wrote:
> Except that the user has the option, via FindQualifiers, to search either by
> name or date.  So, the order of the search is variable.
>
> -----Original Message-----
> From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
> Sent: Friday, August 14, 2009 8:43 AM
> To: juddi-dev@ws.apache.org
> Subject: Re: [DISCUSSION] Finders and sort by - Invalid SQL
>
> OK so we should do a group by then a sortby Modify date; and then by name..
>
>
> Jeff Faath wrote:
>   
>> I forget where, but the spec states that the first name is considered the
>> primary name and therefore the one that should be considered for the sort.
>> Part of the reason why I had that requirement a while back to preserve the
>> order of the various child fields.
>>
>> -----Original Message-----
>> From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
>> Sent: Thursday, August 13, 2009 11:35 PM
>> To: juddi-dev@ws.apache.org
>> Subject: [DISCUSSION] Finders and sort by - Invalid SQL
>>
>> Hi guys,
>>
>> When we do searches for BusinessEntities, BindingTemplates, Services and 
>> TModels we sort by name. However the name is not in the
>> business_entity table, but rather in the business_name, because multiple 
>> business names can be given (for different locales). In the code we
>> select the business entity and then try to sort by name. Some dbs think 
>> this is ok, but in fact most of them do not like it (including Derby). 
>> You get an error which is explained pretty well in this article: 
>>
>>     
> http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by
>   
>> -error.aspx
>>
>> So we can fix this by not doing a "select distinct" but rather doing a 
>> "group by". However it begs the question what it means to
>> sort by name.. I guess you'd only want to sort within one locale, but we 
>> may not always have a name in each locale. So should we simply sort
>> by business_entity key instead?
>>
>> Here are some examples:
>> ./src/main/java/org/apache/juddi/query/FetchBindingTemplatesQuery.java:
>>     
>
>   
>> private static void appendSortTables(DynamicQuery qry)
>> ./src/main/java/org/apache/juddi/query/FetchBusinessEntitiesQuery.java:
>>     
>
>   
>> private static void appendSortTables(DynamicQuery qry)
>> ./src/main/java/org/apache/juddi/query/FetchBusinessServicesQuery.java:
>>     
>
>   
>> private static void appendSortTables(DynamicQuery qry) {
>> ./src/main/java/org/apache/juddi/query/FetchTModelsQuery.java:    
>> private static void appendSortTables(DynamicQuery qry) {
>>
>> --Kurt
>>
>>
>>
>>   
>>     
>
>
>   


RE: [DISCUSSION] Finders and sort by - Invalid SQL

Posted by Jeff Faath <jf...@apache.org>.
Except that the user has the option, via FindQualifiers, to search either by
name or date.  So, the order of the search is variable.

-----Original Message-----
From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
Sent: Friday, August 14, 2009 8:43 AM
To: juddi-dev@ws.apache.org
Subject: Re: [DISCUSSION] Finders and sort by - Invalid SQL

OK so we should do a group by then a sortby Modify date; and then by name..


Jeff Faath wrote:
> I forget where, but the spec states that the first name is considered the
> primary name and therefore the one that should be considered for the sort.
> Part of the reason why I had that requirement a while back to preserve the
> order of the various child fields.
>
> -----Original Message-----
> From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
> Sent: Thursday, August 13, 2009 11:35 PM
> To: juddi-dev@ws.apache.org
> Subject: [DISCUSSION] Finders and sort by - Invalid SQL
>
> Hi guys,
>
> When we do searches for BusinessEntities, BindingTemplates, Services and 
> TModels we sort by name. However the name is not in the
> business_entity table, but rather in the business_name, because multiple 
> business names can be given (for different locales). In the code we
> select the business entity and then try to sort by name. Some dbs think 
> this is ok, but in fact most of them do not like it (including Derby). 
> You get an error which is explained pretty well in this article: 
>
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by
> -error.aspx
>
> So we can fix this by not doing a "select distinct" but rather doing a 
> "group by". However it begs the question what it means to
> sort by name.. I guess you'd only want to sort within one locale, but we 
> may not always have a name in each locale. So should we simply sort
> by business_entity key instead?
>
> Here are some examples:
> ./src/main/java/org/apache/juddi/query/FetchBindingTemplatesQuery.java:

> private static void appendSortTables(DynamicQuery qry)
> ./src/main/java/org/apache/juddi/query/FetchBusinessEntitiesQuery.java:

> private static void appendSortTables(DynamicQuery qry)
> ./src/main/java/org/apache/juddi/query/FetchBusinessServicesQuery.java:

> private static void appendSortTables(DynamicQuery qry) {
> ./src/main/java/org/apache/juddi/query/FetchTModelsQuery.java:    
> private static void appendSortTables(DynamicQuery qry) {
>
> --Kurt
>
>
>
>   



Re: [DISCUSSION] Finders and sort by - Invalid SQL

Posted by Kurt T Stam <ku...@gmail.com>.
OK so we should do a group by then a sortby Modify date; and then by name..


Jeff Faath wrote:
> I forget where, but the spec states that the first name is considered the
> primary name and therefore the one that should be considered for the sort.
> Part of the reason why I had that requirement a while back to preserve the
> order of the various child fields.
>
> -----Original Message-----
> From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
> Sent: Thursday, August 13, 2009 11:35 PM
> To: juddi-dev@ws.apache.org
> Subject: [DISCUSSION] Finders and sort by - Invalid SQL
>
> Hi guys,
>
> When we do searches for BusinessEntities, BindingTemplates, Services and 
> TModels we sort by name. However the name is not in the
> business_entity table, but rather in the business_name, because multiple 
> business names can be given (for different locales). In the code we
> select the business entity and then try to sort by name. Some dbs think 
> this is ok, but in fact most of them do not like it (including Derby). 
> You get an error which is explained pretty well in this article: 
> http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by
> -error.aspx
>
> So we can fix this by not doing a "select distinct" but rather doing a 
> "group by". However it begs the question what it means to
> sort by name.. I guess you'd only want to sort within one locale, but we 
> may not always have a name in each locale. So should we simply sort
> by business_entity key instead?
>
> Here are some examples:
> ./src/main/java/org/apache/juddi/query/FetchBindingTemplatesQuery.java:    
> private static void appendSortTables(DynamicQuery qry)
> ./src/main/java/org/apache/juddi/query/FetchBusinessEntitiesQuery.java:    
> private static void appendSortTables(DynamicQuery qry)
> ./src/main/java/org/apache/juddi/query/FetchBusinessServicesQuery.java:    
> private static void appendSortTables(DynamicQuery qry) {
> ./src/main/java/org/apache/juddi/query/FetchTModelsQuery.java:    
> private static void appendSortTables(DynamicQuery qry) {
>
> --Kurt
>
>
>
>   


RE: [DISCUSSION] Finders and sort by - Invalid SQL

Posted by Jeff Faath <jf...@apache.org>.
I forget where, but the spec states that the first name is considered the
primary name and therefore the one that should be considered for the sort.
Part of the reason why I had that requirement a while back to preserve the
order of the various child fields.

-----Original Message-----
From: Kurt T Stam [mailto:kurt.stam@gmail.com] 
Sent: Thursday, August 13, 2009 11:35 PM
To: juddi-dev@ws.apache.org
Subject: [DISCUSSION] Finders and sort by - Invalid SQL

Hi guys,

When we do searches for BusinessEntities, BindingTemplates, Services and 
TModels we sort by name. However the name is not in the
business_entity table, but rather in the business_name, because multiple 
business names can be given (for different locales). In the code we
select the business entity and then try to sort by name. Some dbs think 
this is ok, but in fact most of them do not like it (including Derby). 
You get an error which is explained pretty well in this article: 
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by
-error.aspx

So we can fix this by not doing a "select distinct" but rather doing a 
"group by". However it begs the question what it means to
sort by name.. I guess you'd only want to sort within one locale, but we 
may not always have a name in each locale. So should we simply sort
by business_entity key instead?

Here are some examples:
./src/main/java/org/apache/juddi/query/FetchBindingTemplatesQuery.java:    
private static void appendSortTables(DynamicQuery qry)
./src/main/java/org/apache/juddi/query/FetchBusinessEntitiesQuery.java:    
private static void appendSortTables(DynamicQuery qry)
./src/main/java/org/apache/juddi/query/FetchBusinessServicesQuery.java:    
private static void appendSortTables(DynamicQuery qry) {
./src/main/java/org/apache/juddi/query/FetchTModelsQuery.java:    
private static void appendSortTables(DynamicQuery qry) {

--Kurt