[xwiki-users] Help with 'order by' clause in HQL
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. -- View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p145569... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi, On Tue, Nov 4, 2008 at 6:04 PM, BrianJones <Brian.Jones@nrc-cnrc.gc.ca> 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-tp1455693p145569... Sent from the XWiki- Users mailing list archive at Nabble.com.
_______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
-- Thomas Mortagne
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 -- View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p145613... Sent from the XWiki- Users mailing list archive at Nabble.com.
On Tue, Nov 4, 2008 at 7:48 PM, BrianJones <Brian.Jones@nrc-cnrc.gc.ca> 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-tp1455693p145613... Sent from the XWiki- Users mailing list archive at Nabble.com.
_______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
-- Thomas Mortagne
Thomas Mortagne wrote:
On Tue, Nov 4, 2008 at 7:48 PM, BrianJones <Brian.Jones@nrc-cnrc.gc.ca> 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/
Thanks Sergiu, your suggestion worked like a charm! Cheers! 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
-- View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p146052... Sent from the XWiki- Users mailing list archive at Nabble.com.
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? -- View this message in context: http://n2.nabble.com/Help-with-%27order-by%27-clause-in-HQL-tp1455693p146099... Sent from the XWiki- Users mailing list archive at Nabble.com.
participants (3)
-
BrianJones -
Sergiu Dumitriu -
Thomas Mortagne