Wiki source code of velocityHqlExamples

Version 31.1 by Marius Dumitru Florea on 2015/02/11

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>>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 === List users currently editing pages ===
154
155 {{code language="none"}}
156 #set($hql = "select distinct lock.userName from XWikiLock lock")
157 {{/code}}
158
159 === List attachments of a page ===
160
161 {{code language="none"}}
162 #set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
163 {{/code}}
164
165 === Statistics ===
166
167 * [[Most Viewed Articles Snippet>>extensions:Extension.Most Viewed Articles]]
168 * [[Most Active Contributors In Group Snippet>>extensions:Extension.Most Active Contributors In Group]]
169 * [[Number Of Active Users Per Day And Per Week Snippet>>extensions:Extension.Number Of Active Users Per Day And Per Week]]
170 * [[Number Of Edited Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Edited Articles Per Day And Per Week]]
171 * [[Number Of Created Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Created Articles Per Day And Per Week]]
172 * [[Number Of Deleted Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Deleted Articles Per Day And Per Week]]
173
174 == Non-exhaustive list of queryable Object Fields ==
175
176 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).
177
178 === XWikiDocument ===
179
180 * **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome##
181 * XWikiDocument.author : last editor. Example value: ##XWiki.Admin##
182 * XWikiDocument.creator : first editor. Example value: ##XWiki.Admin##
183
184 === BaseObject ===
185
186 * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789##
187 * BaseObject.className : class. Example value: ##XWiki.XWikiUsers##
188
189 === *Property (StringProperty, IntegerProperty, etc) ===
190
191 * **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789##
192 * Property.name : name of the property. Example value: ##first_name##
193 * Property.value : value. Example value: ##John##

Get Connected