Re: [xwiki-devs] How to write HQL queries for recently modified pages under a specific space

On Thu, Aug 11, 2011 at 1:03 AM, Sergiu Dumitriu &lt;sergiu(a)xwiki.com&gt; wrote: I have tried both, but all failed, the error infomation is bellow: Failed to execute the [velocity] macroorg.xwiki.rendering.macro.MacroExecutionException: Failed to evaluate Velocity Macro for content [#set($media = "$!request.media") #set($nb = "$!request.nb") #if($nb != '') #set($nb = $util.parseInt($nb) + 1) #else #set($nb = 5) #end #if ($media == 'json') #set($discard = $response.setContentType("application/json")) #set ($recentlyModifiedPage = $xwiki.searchDocuments('where where doc.space=''Main'' order by doc.date desc order by doc.date desc', $nb, 0)) #set($size = $recentlyModifiedPage.size()) [ #foreach ($docname in $recentlyModifiedPage) #if ($xwiki.hasAccessLevel('view', $xcontext.getUser(), $docname)) #set ($rdoc = $xwiki.getDocument($docname)) #set($name = $rdoc.getName()) #set($fullname = "$rdoc.getWiki():$rdoc.getSpace().$rdoc.getName()") {"info":"${escapetool.javascript($rdoc.fullName)}", "path":"${escapetool.javascript($fullname)}", "name":"$name", "type":"wikipage"} #if($velocityCount < $size) , #end #end #end #set($result = $services.query.hql('select doc.fullName, doc.name, attach.filename from XWikiDocument as doc, XWikiAttachment as attach where attach.docId=doc.id order by attach.date desc').setLimit($nb).execute()) #foreach($item in $result) #if($size > 0 || $velocityCount > 1) , #end {"name":"$item.get(2)", "path":"${escapetool.javascript($item.get(0))}", "info":"${escapetool.javascript($item.get(0))}", "type":"attachment"} #end ] #else #set ($recentlyModified = $xwiki.searchDocuments('where 1=1 order by doc.date desc', $nb, 0)) #if ($recentlyModified.size() > 0 || $showEmptyPanels) #panelheader($msg.get('panels.recentlyModified.title')) #foreach ($docname in $recentlyModified) #if ($xwiki.hasAccessLevel('view', $xcontext.getUser(), $docname)) #set ($rdoc = $xwiki.getDocument($docname).getTranslatedDocument()) #if ($foreach.index > 0) | #end $rdoc.plainTitle #end #end #panelfooter() #end #end] at org.xwiki.rendering.internal.macro.velocity.VelocityMacro.evaluateString(VelocityMacro.java:133) at org.xwiki.rendering.internal.macro.velocity.VelocityMacro.evaluateString(VelocityMacro.java:49) at org.xwiki.rendering.macro.script.AbstractScriptMacro.evaluateBlock(AbstractScriptMacro.java:306) at org.xwiki.rendering.macro.script.AbstractScriptMacro.execute(AbstractScriptMacro.java:202) at org.xwiki.rendering.macro.script.AbstractScriptMacro.execute(AbstractScriptMacro.java:59) at org.xwiki.rendering.internal.transformation.macro.MacroTransformation.transformOnce(MacroTransformation.java:196) at org.xwiki.rendering.internal.transformation.macro.MacroTransformation.transform(MacroTransformation.java:141) at org.xwiki.rendering.internal.transformation.DefaultTransformationManager.performTransformations(DefaultTransformationManager.java:81) at com.xpn.xwiki.doc.XWikiDocument.performSyntaxConversion(XWikiDocument.java:7694) at com.xpn.xwiki.doc.XWikiDocument.performSyntaxConversion(XWikiDocument.java:7643) at com.xpn.xwiki.doc.XWikiDocument.getRenderedContent(XWikiDocument.java:960) at com.xpn.xwiki.api.Document.getRenderedContent(Document.java:616) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:395) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:384) at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:173) at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:280) at org.apache.velocity.runtime.parser.node.ASTReference.render(ASTReference.java:369) at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72) at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:87) at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:342) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:195) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:160) at org.xwiki.rendering.internal.macro.velocity.VelocityMacro.evaluateString(VelocityMacro.java:126) at org.xwiki.rendering.internal.macro.velocity.VelocityMacro.evaluateString(VelocityMacro.java:49) at org.xwiki.rendering.macro.script.AbstractScriptMacro.evaluateBlock(AbstractScriptMacro.java:306) at org.xwiki.rendering.macro.script.AbstractScriptMacro.execute(AbstractScriptMacro.java:202) at org.xwiki.rendering.macro.script.AbstractScriptMacro.execute(AbstractScriptMacro.java:59) at org.xwiki.rendering.internal.transformation.macro.MacroTransformation.transformOnce(MacroTransformation.java:196) at org.xwiki.rendering.internal.transformation.macro.MacroTransformation.transform(MacroTransformation.java:141) at org.xwiki.rendering.internal.transformation.DefaultTransformationManager.performTransformations(DefaultTransformationManager.java:81) at com.xpn.xwiki.doc.XWikiDocument.performSyntaxConversion(XWikiDocument.java:7694) at com.xpn.xwiki.doc.XWikiDocument.performSyntaxConversion(XWikiDocument.java:7643) at com.xpn.xwiki.doc.XWikiDocument.getRenderedContent(XWikiDocument.java:850) at com.xpn.xwiki.doc.XWikiDocument.getRenderedContent(XWikiDocument.java:798) at com.xpn.xwiki.api.Document.getRenderedContent(Document.java:626) at sun.reflect.GeneratedMethodAccessor147.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:395) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:384) at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:173) at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:280) at org.apache.velocity.runtime.parser.node.ASTReference.render(ASTReference.java:369) at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72) at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:87) at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:342) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:195) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:160) at com.xpn.xwiki.render.XWikiVelocityRenderer.evaluate(XWikiVelocityRenderer.java:116) at com.xpn.xwiki.XWiki.evaluateTemplate(XWiki.java:1836) at com.xpn.xwiki.XWiki.parseTemplate(XWiki.java:1773) at com.xpn.xwiki.api.XWiki.parseTemplate(XWiki.java:836) at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:395) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:384) at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:173) at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:280) at org.apache.velocity.runtime.parser.node.ASTReference.render(ASTReference.java:369) at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72) at org.apache.velocity.runtime.directive.VelocimacroProxy.render(VelocimacroProxy.java:216) at org.apache.velocity.runtime.directive.RuntimeMacro.render(RuntimeMacro.java:311) at org.apache.velocity.runtime.directive.RuntimeMacro.render(RuntimeMacro.java:230) at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:207) at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72) at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:342) at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:106) at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:342) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:195) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:160) at com.xpn.xwiki.render.XWikiVelocityRenderer.evaluate(XWikiVelocityRenderer.java:116) at com.xpn.xwiki.XWiki.evaluateTemplate(XWiki.java:1836) at com.xpn.xwiki.web.Utils.parseTemplate(Utils.java:153) at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:224) at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:115) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166) at com.xpn.xwiki.web.ActionFilter.doFilter(ActionFilter.java:129) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.xwiki.wysiwyg.server.filter.ConversionFilter.doFilter(ConversionFilter.java:152) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.xpn.xwiki.plugin.webdav.XWikiDavFilter.doFilter(XWikiDavFilter.java:68) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.xwiki.container.servlet.filters.internal.SavedRequestRestorerFilter.doFilter(SavedRequestRestorerFilter.java:218) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.xwiki.container.servlet.filters.internal.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:112) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:326) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:536) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:915) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:539) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:405) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582) Caused by: org.xwiki.velocity.XWikiVelocityException: Failed to evaluate content with id [xwiki:Panels.Recently Modified] at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:204) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:160) at org.xwiki.rendering.internal.macro.velocity.VelocityMacro.evaluateString(VelocityMacro.java:126) ... 113 more Caused by: org.apache.velocity.exception.MethodInvocationException: Invocation of method 'searchDocuments' 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 doc.space, doc.name, doc.date desc order by doc.date from XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and where doc.space='Main' order by doc.date desc order by doc.date desc] Wrapped Exception: unexpected token: desc near line 1, column 47 [select distinct doc.space, doc.name, doc.date desc order by doc.date from com.xpn.xwiki.doc.XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and where doc.space='Main' order by doc.date desc order by doc.date desc] at xwiki:Panels.Recently Modified[line 11, column 38] at org.apache.velocity.runtime.parser.node.ASTMethod.handleInvocationException(ASTMethod.java:243) at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:187) at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:280) at org.apache.velocity.runtime.parser.node.ASTReference.value(ASTReference.java:567) at org.apache.velocity.runtime.parser.node.ASTExpression.value(ASTExpression.java:71) at org.apache.velocity.runtime.parser.node.ASTSetDirective.render(ASTSetDirective.java:142) at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72) at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:87) at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:342) at org.xwiki.velocity.internal.DefaultVelocityEngine.evaluate(DefaultVelocityEngine.java:195) ... 115 more Caused by: com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception while searching documents with SQL [select distinct doc.space, doc.name, doc.date desc order by doc.date from XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and where doc.space='Main' order by doc.date desc order by doc.date desc] Wrapped Exception: unexpected token: desc near line 1, column 47 [select distinct doc.space, doc.name, doc.date desc order by doc.date from com.xpn.xwiki.doc.XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and where doc.space='Main' order by doc.date desc order by doc.date desc] at com.xpn.xwiki.store.XWikiHibernateStore.searchGenericInternal(XWikiHibernateStore.java:2352) at com.xpn.xwiki.store.XWikiHibernateStore.searchDocumentReferencesInternal(XWikiHibernateStore.java:2308) at com.xpn.xwiki.store.XWikiHibernateStore.searchDocumentsNamesInternal(XWikiHibernateStore.java:2295) at com.xpn.xwiki.store.XWikiHibernateStore.searchDocumentsNames(XWikiHibernateStore.java:2073) at com.xpn.xwiki.store.XWikiHibernateStore.searchDocumentsNames(XWikiHibernateStore.java:2052) at com.xpn.xwiki.store.XWikiCacheStore.searchDocumentsNames(XWikiCacheStore.java:378) at com.xpn.xwiki.api.XWiki.searchDocuments(XWiki.java:623) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:395) at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:384) at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:173) ... 123 more -- Best wishes, 许凌志(Jame Xu) MOE KLINNS Lab and SKLMS Lab, Xi'an Jiaotong University Department of Computer Science and Technology, Xi’an Jiaotong University