Help with 'order by' clause in HQL

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with 'order by' clause in HQL

BrianJones
Hello again,

Quick question, regarding the built-in 'Tags' page, that builds a list of all the tags being used in the XWiki instance.

I would like to have this list being rendered in alphabetical order, instead of in a random order (or by creation date), but all the 'order by' clauses I've tried have either not worked at all, not worked in the desired mannor, or thrown exceptions.

The HQL statement provided by XWiki is as follows:

#set($sql = "select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' order by obj asc")

And I've tried the following 'order by' clauses:

order by obj.name asc
order by obj.fullName asc
order by obj asc
order by doc.name asc
order by doc.fullName asc
order by doc asc
order by prop asc
order by prop.value asc


None of these seem to produce the list in alphabetical order, although some of them do change the order in which the tags are displayed.  Any ideas on how to get this order by clause working to produce the tag list in alphabetical order?

I have a feeling my difficulty lies in that the property is a DBStringListProperty in the database, but I'm not sure how to work with these.

Thanks ahead of time guys.
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

Thomas Mortagne
Administrator
Hi,

On Tue, Nov 4, 2008 at 6:04 PM, BrianJones <[hidden email]> wrote:

>
> Hello again,
>
> Quick question, regarding the built-in 'Tags' page, that builds a list of
> all the tags being used in the XWiki instance.
>
> I would like to have this list being rendered in alphabetical order, instead
> of in a random order (or by creation date), but all the 'order by' clauses
> I've tried have either not worked at all, not worked in the desired mannor,
> or thrown exceptions.
>
> The HQL statement provided by XWiki is as follows:
>
> #set($sql = "select distinct elements(prop.list) from BaseObject as obj,
> DBStringListProperty as prop where obj.className='XWiki.TagClass' and
> obj.id=prop.id.id and prop.id.name='tags' order by obj asc")
>
> And I've tried the following 'order by' clauses:
>
> order by obj.name asc
> order by obj.fullName asc
> order by obj asc
> order by doc.name asc
> order by doc.fullName asc
> order by doc asc
> order by prop asc
> order by prop.value asc

The prop contains all the tags of one document not just one tag.

I you want to get all tags ordered alphabetically, I think the
following should do the trick :

#set($sql = "select distinct elements(prop.list) as tagvalue from
BaseObject as obj,
 DBStringListProperty as prop where obj.className='XWiki.TagClass' and
 obj.id=prop.id.id and prop.id.name='tags' order by tagvalue asc")

>
> None of these seem to produce the list in alphabetical order, although some
> of them do change the order in which the tags are displayed.  Any ideas on
> how to get this order by clause working to produce the tag list in
> alphabetical order?
>
> I have a feeling my difficulty lies in that the property is a
> DBStringListProperty in the database, but I'm not sure how to work with
> these.
>
> Thanks ahead of time guys.
> --
> View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p1455693.html
> Sent from the XWiki- Users mailing list archive at Nabble.com.
>
> _______________________________________________
> users mailing list
> [hidden email]
> http://lists.xwiki.org/mailman/listinfo/users
>



--
Thomas Mortagne
_______________________________________________
users mailing list
[hidden email]
http://lists.xwiki.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

BrianJones
Thomas Mortagne wrote
The prop contains all the tags of one document not just one tag.

I you want to get all tags ordered alphabetically, I think the
following should do the trick :

#set($sql = "select distinct elements(prop.list) as tagvalue from
BaseObject as obj,
 DBStringListProperty as prop where obj.className='XWiki.TagClass' and
 obj.id=prop.id.id and prop.id.name='tags' order by tagvalue asc")
Thanks for your reply Thomas, your suggestion seems to make sense to me, but when I tested it out, I get an error in the Tomcat window, and the page doesn't render.  The error is as follows:

...
Cause by: org.apahace.velocity.exception.MethodInvocationException: Invocation of method 'search' in class com.xpn.xwiki.api.XWiki threw exception com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception while searching documents with sql select distinct elements(prop.list) as tagvalue from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prod.id.name='tags' order by tagvalue asc
Wrapped Exception: could not execute query @ Main.Tags[5,22]
...


Any ideas? Thanks again
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

Thomas Mortagne
Administrator
On Tue, Nov 4, 2008 at 7:48 PM, BrianJones <[hidden email]> wrote:

>
>
> Thomas Mortagne wrote:
>>
>> The prop contains all the tags of one document not just one tag.
>>
>> I you want to get all tags ordered alphabetically, I think the
>> following should do the trick :
>>
>> #set($sql = "select distinct elements(prop.list) as tagvalue from
>> BaseObject as obj,
>>  DBStringListProperty as prop where obj.className='XWiki.TagClass' and
>>  obj.id=prop.id.id and prop.id.name='tags' order by tagvalue asc")
>>
>
> Thanks for your reply Thomas, your suggestion seems to make sense to me, but
> when I tested it out, I get an error in the Tomcat window, and the page
> doesn't render.  The error is as follows:
>
> ...
> Cause by: org.apahace.velocity.exception.MethodInvocationException:
> Invocation of method 'search' in class com.xpn.xwiki.api.XWiki threw
> exception com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception
> while searching documents with sql select distinct elements(prop.list) as
> tagvalue from BaseObject as obj, DBStringListProperty as prop where
> obj.className='XWiki.TagClass' and obj.id=prop.id.id and prod.id.name='tags'
> order by tagvalue asc
> Wrapped Exception: could not execute query @ Main.Tags[5,22]
> ...

I guess there is a syntax issue somewhere, don't you have more
information in the log file ?

>
> Any ideas? Thanks again
>
> --
> View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p1456132.html
> Sent from the XWiki- Users mailing list archive at Nabble.com.
>
> _______________________________________________
> users mailing list
> [hidden email]
> http://lists.xwiki.org/mailman/listinfo/users
>



--
Thomas Mortagne
_______________________________________________
users mailing list
[hidden email]
http://lists.xwiki.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

Sergiu Dumitriu-2
Thomas Mortagne wrote:

> On Tue, Nov 4, 2008 at 7:48 PM, BrianJones <[hidden email]> wrote:
>>
>> Thomas Mortagne wrote:
>>> The prop contains all the tags of one document not just one tag.
>>>
>>> I you want to get all tags ordered alphabetically, I think the
>>> following should do the trick :
>>>
>>> #set($sql = "select distinct elements(prop.list) as tagvalue from
>>> BaseObject as obj,
>>>  DBStringListProperty as prop where obj.className='XWiki.TagClass' and
>>>  obj.id=prop.id.id and prop.id.name='tags' order by tagvalue asc")
>>>
>> Thanks for your reply Thomas, your suggestion seems to make sense to me, but
>> when I tested it out, I get an error in the Tomcat window, and the page
>> doesn't render.  The error is as follows:
>>
>> ...
>> Cause by: org.apahace.velocity.exception.MethodInvocationException:
>> Invocation of method 'search' in class com.xpn.xwiki.api.XWiki threw
>> exception com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception
>> while searching documents with sql select distinct elements(prop.list) as
>> tagvalue from BaseObject as obj, DBStringListProperty as prop where
>> obj.className='XWiki.TagClass' and obj.id=prop.id.id and prod.id.name='tags'
>> order by tagvalue asc
>> Wrapped Exception: could not execute query @ Main.Tags[5,22]
>> ...
>
> I guess there is a syntax issue somewhere, don't you have more
> information in the log file ?

Try:

select distinct elements(prop.list) from BaseObject as obj,
  DBStringListProperty as prop where obj.className='XWiki.TagClass' and
  obj.id=prop.id.id and prop.id.name='tags' order by 1 asc

--
Sergiu Dumitriu
http://purl.org/net/sergiu/
_______________________________________________
users mailing list
[hidden email]
http://lists.xwiki.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

BrianJones
This post was updated on .
Thanks Sergiu, your suggestion worked like a charm!

Sergiu Dumitriu-2 wrote
Try:

select distinct elements(prop.list) from BaseObject as obj,
  DBStringListProperty as prop where obj.className='XWiki.TagClass' and
  obj.id=prop.id.id and prop.id.name='tags' order by 1 asc
I have one other question to ask regarding this HQL query.  Many of the tags I'm using have a common prefix (for example one group of tags may have the prefix 'xyz-', and another group may have the prefix 'zyx-').

How would I go about appending another WHERE clause in the HQL query to only select the tags with the prefix 'xyz-'?

If you could help me out with this I would he forever in debted to you!  Thanks again, I will try to hack around this idea in the meantime, and post whatever methods I've tried (and likely failed with).

I know it's possible to use the 'LIKE' operator with HQL, and I've tried a query like this:

select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.value like 'xyz-%' order by 1 asc

But unfortunately this didn't work for me, the page wouldn't render and I get the following error in the Tomcat window:

...
Cause by: com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception while searching documents with sql select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.value like 'xyz-%' order by 1 asc
Wrapped exception: could not resolve property: value of: com.xpn.xwiki.objects.BaseObject [... same select statement ...]


It would be really easy to do with a SQL statement like:

SELECT DISTINCT xwl_value
FROM xwikilistitems
WHERE xwl_value LIKE 'xyz-%'
ORDER BY xwl_value ASC;


But I don't think that is possible....  Or is it?

Any ideas?  Cheers to the community!
Reply | Threaded
Open this post in threaded view
|

Re: Help with 'order by' clause in HQL

BrianJones
BrianJones wrote
I have one other question to ask regarding this HQL query.  Many of the tags I'm using have a common prefix (for example one group of tags may have the prefix 'xyz-', and another group may have the prefix 'zyx-').

How would I go about appending another WHERE clause in the HQL query to only select the tags with the prefix 'xyz-'?
I found a topic in the archives that was similar to my problem, and it suggested using the following query:

select distinct elements(prop.list) from BaseObject as obj, DBStringListProperty as prop where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags' and obj.name in (select obj1.name from BaseObject as obj1, DBStringListProperty as prop1 join prop1.list list where obj1.id=prop1.id.id and prop1.id.name='tags' and list like 'xyz-%') order by 1 asc

Although this query seems to be valid, the page renders, and there are no errors in the Tomcat menu, it doesn't provide the functionality I'm looking for.  The list returned is definatly a slimmed down list from the 'All tags' list, but it does NOT limit the list to only those tags with the 'xyz-' prefix.

Any suggestions?