Wiki source code of velocityHqlExamples
Version 34.1 by Ecaterina Moraru (Valica) on 2017/09/01
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} | ||
| 2 | {{toc start="2" depth="4"/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| 5 | = HQL Query Examples in Velocity = | ||
| 6 | |||
| 7 | {{warning}} | ||
| 8 | The examples on this page are using deprecated search APIs (##$xwiki.searchDocuments## and ##$xwiki.search##). If you want to query the XWiki database then you should use the [[Query Module>>extensions:Extension.Query Module]] instead. If you want to perform a full text search on the XWiki model then you can use the [[Solr Search API>>extensions:Extension.Solr Search Query API]]. | ||
| 9 | {{/warning}} | ||
| 10 | |||
| 11 | XWiki allows user to access documents and objects with [[HQL>>http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html]] queries in [[Velocity>>http://jakarta.apache.org/velocity/docs/user-guide.html]] scripts. | ||
| 12 | |||
| 13 | == Public API (##searchDocuments##) == | ||
| 14 | |||
| 15 | {{velocity}} | ||
| 16 | {{html}} | ||
| 17 | #info("With this API the query consist in the WHERE condition of a full HQL query. Any user with edit rights can write a script using this API. Any user with view rights can view the result of such a query.") | ||
| 18 | {{/html}} | ||
| 19 | {{/velocity}} | ||
| 20 | |||
| 21 | General example showing how to display the first 5 results of a given query: | ||
| 22 | |||
| 23 | {{code language="none"}} | ||
| 24 | #set($hql = "<query here>") | ||
| 25 | #set($results = $xwiki.searchDocuments($hql, 5, 0)) | ||
| 26 | #foreach ($item in $results) | ||
| 27 | * $item | ||
| 28 | #end | ||
| 29 | {{/code}} | ||
| 30 | |||
| 31 | The examples below will show you various HQL queries that you can write. | ||
| 32 | |||
| 33 | === Simple Query === | ||
| 34 | |||
| 35 | Displays all documents who have been created by the user ##XWiki.JohnDoe##: | ||
| 36 | |||
| 37 | {{code language="none"}} | ||
| 38 | #set($hql = "where doc.creator='XWiki.JohnDoe'") | ||
| 39 | {{/code}} | ||
| 40 | |||
| 41 | === Ordered Query === | ||
| 42 | |||
| 43 | Displays all documents who have been created by the user ##XWiki.JohnDoe## and sorted by document's last modification date, in ascending order: | ||
| 44 | |||
| 45 | {{code language="none"}} | ||
| 46 | #set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc") | ||
| 47 | {{/code}} | ||
| 48 | |||
| 49 | === Advanced Query (date & time) === | ||
| 50 | |||
| 51 | {{velocity}} | ||
| 52 | {{html wiki="true"}} | ||
| 53 | #info("Since there is no [[standard way to calculate dates interval in HQL>>http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434]] those queries are a bit unnatural.") | ||
| 54 | {{/html}} | ||
| 55 | {{/velocity}} | ||
| 56 | |||
| 57 | {{code language="none"}} | ||
| 58 | #set($hql = "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1) order by doc.date desc") | ||
| 59 | {{/code}} | ||
| 60 | |||
| 61 | |||
| 62 | === Query documents older than 3 days === | ||
| 63 | |||
| 64 | The following snippet deletes all documents in the space TempDocs that are older than 3 days. | ||
| 65 | For the computation of the relative age, a calendar object created by the $datetool is used and passed to the hibernate query. Tested with MySQL only. | ||
| 66 | |||
| 67 | {{code language="none"}} | ||
| 68 | #set($hql="select doc.fullName from XWikiDocument as doc where doc.web = ? and doc.creationDate < ? ") | ||
| 69 | #set($cal = $datetool.systemCalendar)$cal.add(7,-3) | ||
| 70 | #set($olderTempDocs=$xwiki.search($hql,5000, 0, ["TempdDocs",$datetool.toDate($cal)])) | ||
| 71 | #foreach($x in $olderTempDocs)$xwiki.getDocument($x).deleteWithProgrammingRights()#end | ||
| 72 | {{/code}} | ||
| 73 | |||
| 74 | |||
| 75 | Other examples: | ||
| 76 | |||
| 77 | * Listing all documents modified during the current day: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc{{/code}} | ||
| 78 | * Listing all documents modified during the current week: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc{{/code}} | ||
| 79 | * Listing all documents modified during the current month: {{code language="none"}}where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc{{/code}} | ||
| 80 | |||
| 81 | == Privileged API (##search##) == | ||
| 82 | |||
| 83 | {{velocity}} | ||
| 84 | {{html wiki="true"}} | ||
| 85 | #warning("Calls to the privileged API are only executed when the calling page has been saved by a user with **Programming Rights**. The reason is that search can be used to send dangerous HQL command like update, delete, etc.") | ||
| 86 | {{/html}} | ||
| 87 | {{/velocity}} | ||
| 88 | |||
| 89 | General example showing how to display the first 5 results of a given query: | ||
| 90 | |||
| 91 | {{code language="none"}} | ||
| 92 | #set($hql = "<query here>") | ||
| 93 | #set($results = $xwiki.search($hql, 5, 0)) | ||
| 94 | #foreach ($item in $results) | ||
| 95 | * $item | ||
| 96 | #end | ||
| 97 | {{/code}} | ||
| 98 | |||
| 99 | The examples below will show you various HQL queries that you can write. | ||
| 100 | |||
| 101 | === Simple Query === | ||
| 102 | |||
| 103 | {{code language="none"}} | ||
| 104 | #set($hql = "select doc.name from XWikiDocument doc") | ||
| 105 | {{/code}} | ||
| 106 | |||
| 107 | === Count Query === | ||
| 108 | |||
| 109 | {{code language="none"}} | ||
| 110 | #set($results = $xwiki.search("select count(doc) from XWikiDocument doc")) | ||
| 111 | ## Since $xwiki.search is returning a list, we get its first element | ||
| 112 | Count : $results.get(0) | ||
| 113 | {{/code}} | ||
| 114 | |||
| 115 | === Simple Query with multiple fields === | ||
| 116 | |||
| 117 | {{code language="none"}} | ||
| 118 | #set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0)) | ||
| 119 | #foreach ($row in $results) | ||
| 120 | #foreach ($col in $row) | ||
| 121 | #if ($velocityCount == 1) | ||
| 122 | #set($docName = $col) | ||
| 123 | #elseif ($velocityCount == 2) | ||
| 124 | #set($docDate = $col) | ||
| 125 | #end | ||
| 126 | #end | ||
| 127 | $docName : $docDate <br/> | ||
| 128 | #end | ||
| 129 | {{/code}} | ||
| 130 | |||
| 131 | === Getting objects of a specific class === | ||
| 132 | |||
| 133 | {{code language="none"}} | ||
| 134 | #set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'") | ||
| 135 | {{/code}} | ||
| 136 | |||
| 137 | === Getting objects' properties === | ||
| 138 | |||
| 139 | {{code language="none"}} | ||
| 140 | #set($hql = "select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'") | ||
| 141 | {{/code}} | ||
| 142 | |||
| 143 | === Getting documents where objects' properties equals some value === | ||
| 144 | |||
| 145 | {{code language="none"}} | ||
| 146 | #set($hql = "select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'") | ||
| 147 | {{/code}} | ||
| 148 | |||
| 149 | {{code language="none"}} | ||
| 150 | #set($hql = ", BaseObject as obj, StringProperty as firstName, StringProperty as lastName where doc.fullName = obj.name and obj.className='XWiki.XWikiUsers' and obj.id=firstName.id.id and firstName.id.name='first_name' and obj.id=lastName.id.id and lastName.id.name='last_name' and firstName.value like 'A%' and lastName.value like 'B%' order by doc.fullName asc") | ||
| 151 | {{/code}} | ||
| 152 | |||
| 153 | |||
| 154 | {{code language="none"}} | ||
| 155 | #set($hql = "select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'") | ||
| 156 | {{/code}} | ||
| 157 | |||
| 158 | If property is multiselect Page type, relational storage must be checked, if not, the content is not stored the same way and we cannot do "join prop.list as list" | ||
| 159 | {{code language="none"}}#set ($hql ="select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='MySpace.MyClass' and obj.id=prop.id.id and prop.id.name='MyPropertyName' and list = 'MyValueInMyPropertyName'"){{/code}} | ||
| 160 | |||
| 161 | === List users currently editing pages === | ||
| 162 | |||
| 163 | {{code language="none"}} | ||
| 164 | #set($hql = "select distinct lock.userName from XWikiLock lock") | ||
| 165 | {{/code}} | ||
| 166 | |||
| 167 | === List attachments of a page === | ||
| 168 | |||
| 169 | {{code language="none"}} | ||
| 170 | #set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id") | ||
| 171 | {{/code}} | ||
| 172 | |||
| 173 | === Statistics === | ||
| 174 | |||
| 175 | * [[Most Viewed Articles Snippet>>snippets:Extension.Most Viewed Articles]] | ||
| 176 | * [[Most Active Contributors In Group Snippet>>snippets:Extension.Most Active Contributors In Group]] | ||
| 177 | * [[Number Of Active Users Per Day And Per Week Snippet>>snippets:Extension.Number Of Active Users Per Day And Per Week]] | ||
| 178 | * [[Number Of Edited Articles Per Day And Per Week Snippet>>snippets:Extension.Number Of Edited Articles Per Day And Per Week]] | ||
| 179 | * [[Number Of Created Articles Per Day And Per Week Snippet>>snippets:Extension.Number Of Created Articles Per Day And Per Week]] | ||
| 180 | * [[Number Of Deleted Articles Per Day And Per Week Snippet>>snippets:Extension.Number of Deleted Articles Per Day And Per Week]] | ||
| 181 | |||
| 182 | == Non-exhaustive list of queryable Object Fields == | ||
| 183 | |||
| 184 | The full list of available fields can be found in the Hibernate mapping files (*.hbm.xml), which can be found inside WEB-INF/lib/xwiki-core-x.y.jar (where x.y is the XWiki version). | ||
| 185 | |||
| 186 | === XWikiDocument === | ||
| 187 | |||
| 188 | * **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome## | ||
| 189 | * XWikiDocument.author : last editor. Example value: ##XWiki.Admin## | ||
| 190 | * XWikiDocument.creator : first editor. Example value: ##XWiki.Admin## | ||
| 191 | |||
| 192 | === BaseObject === | ||
| 193 | |||
| 194 | * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789## | ||
| 195 | * BaseObject.className : class. Example value: ##XWiki.XWikiUsers## | ||
| 196 | |||
| 197 | === *Property (StringProperty, IntegerProperty, etc) === | ||
| 198 | |||
| 199 | * **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789## | ||
| 200 | * Property.name : name of the property. Example value: ##first_name## | ||
| 201 | * Property.value : value. Example value: ##John## |
