Skip to Content
Last modified by Manuel Leduc on 2023/02/02 10:26
Show last authors
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>>https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html]] queries in [[Velocity>>http://velocity.apache.org/engine/1.7/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
160 {{code language="none"}}
161 #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'")
162 {{/code}}
163
164 === List users currently editing pages ===
165
166 {{code language="none"}}
167 #set($hql = "select distinct lock.userName from XWikiLock lock")
168 {{/code}}
169
170 === List attachments of a page ===
171
172 {{code language="none"}}
173 #set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
174 {{/code}}
175
176 === Statistics ===
177
178 * [[Most Viewed Articles Snippet>>snippets:Extension.Most Viewed Articles]]
179 * [[Most Active Contributors In Group Snippet>>snippets:Extension.Most Active Contributors In Group]]
180 * [[Number Of Active Users Per Day And Per Week Snippet>>snippets:Extension.Number Of Active Users Per Day And Per Week]]
181 * [[Number Of Edited Articles Per Day And Per Week Snippet>>snippets:Extension.Number Of Edited Articles Per Day And Per Week]]
182 * [[Number Of Created Articles Per Day And Per Week Snippet>>snippets:Extension.Number Of Created Articles Per Day And Per Week]]
183 * [[Number Of Deleted Articles Per Day And Per Week Snippet>>snippets:Extension.Number of Deleted Articles Per Day And Per Week]]
184
185 == Non-exhaustive list of queryable Object Fields ==
186
187 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).
188
189 === XWikiDocument ===
190
191 * **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome##
192 * XWikiDocument.author : last editor. Example value: ##XWiki.Admin##
193 * XWikiDocument.creator : first editor. Example value: ##XWiki.Admin##
194
195 === BaseObject ===
196
197 * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789##
198 * BaseObject.className : class. Example value: ##XWiki.XWikiUsers##
199
200 === *Property (StringProperty, IntegerProperty, etc) ===
201
202 * **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789##
203 * Property.name : name of the property. Example value: ##first_name##
204 * Property.value : value. Example value: ##John##

About

About

Support

Platform

User Guide

Admin Guide

Developer Guide

Projects

XWiki

Extensions

Other

Contribute

Status

Practices

Under the Hood

Get Involved

Get Connected