Hide last authors
Flavius Olaru 27.1 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc start="2" depth="4"/}}
3 {{/box}}
Vincent Massol 17.1 4
Vincent Massol 20.1 5 = HQL Query Examples in Velocity =
Jean-Vincent Drean 1.1 6
Marius Dumitru Florea 31.1 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
Martin Simmons 35.4 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.
Jean-Vincent Drean 1.1 12
Vincent Massol 22.1 13 == Public API (##searchDocuments##) ==
14
Flavius Olaru 27.1 15 {{velocity}}
16 {{html}}
Vincent Massol 22.1 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.")
Flavius Olaru 27.1 18 {{/html}}
19 {{/velocity}}
Vincent Massol 22.1 20
Vincent Massol 21.1 21 General example showing how to display the first 5 results of a given query:
Jean-Vincent Drean 1.3 22
Vincent Massol 21.1 23 {{code language="none"}}
Vincent Massol 22.2 24 #set($hql = "<query here>")
Vincent Massol 21.1 25 #set($results = $xwiki.searchDocuments($hql, 5, 0))
Jean-Vincent Drean 1.3 26 #foreach ($item in $results)
Sergiu Dumitriu 4.5 27 * $item
Jean-Vincent Drean 1.3 28 #end
Vincent Massol 15.1 29 {{/code}}
Jean-Vincent Drean 1.3 30
Vincent Massol 21.1 31 The examples below will show you various HQL queries that you can write.
32
Vincent Massol 15.1 33 === Simple Query ===
34
Vincent Massol 21.1 35 Displays all documents who have been created by the user ##XWiki.JohnDoe##:
Jean-Vincent Drean 1.3 36
Vincent Massol 21.1 37 {{code language="none"}}
38 #set($hql = "where doc.creator='XWiki.JohnDoe'")
39 {{/code}}
40
Vincent Massol 15.1 41 === Ordered Query ===
42
Vincent Massol 21.1 43 Displays all documents who have been created by the user ##XWiki.JohnDoe## and sorted by document's last modification date, in ascending order:
Jean-Vincent Drean 1.21 44
Vincent Massol 21.1 45 {{code language="none"}}
46 #set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc")
47 {{/code}}
48
Vincent Massol 15.1 49 === Advanced Query (date & time) ===
Jean-Vincent Drean 1.35 50
Flavius Olaru 27.1 51 {{velocity}}
52 {{html wiki="true"}}
Vincent Massol 21.1 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.")
Flavius Olaru 27.1 54 {{/html}}
55 {{/velocity}}
Jean-Vincent Drean 1.21 56
Vincent Massol 21.1 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")
Vincent Massol 15.1 59 {{/code}}
Jean-Vincent Drean 1.35 60
Paul Libbrecht 30.1 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
Vincent Massol 21.1 75 Other examples:
Flavius Olaru 27.1 76
Vincent Massol 21.1 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}}
Jean-Vincent Drean 1.35 80
Vincent Massol 22.3 81 == Privileged API (##search##) ==
Jean-Vincent Drean 1.3 82
Flavius Olaru 27.1 83 {{velocity}}
84 {{html wiki="true"}}
Jean SIMARD 29.1 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.")
Flavius Olaru 27.1 86 {{/html}}
87 {{/velocity}}
Jean-Vincent Drean 1.1 88
Vincent Massol 22.1 89 General example showing how to display the first 5 results of a given query:
90
91 {{code language="none"}}
Vincent Massol 22.2 92 #set($hql = "<query here>")
Vincent Massol 22.1 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
Vincent Massol 15.1 101 === Simple Query ===
102
Vincent Massol 21.1 103 {{code language="none"}}
104 #set($hql = "select doc.name from XWikiDocument doc")
105 {{/code}}
Jean-Vincent Drean 1.1 106
Vincent Massol 15.1 107 === Count Query ===
Jean-Vincent Drean 1.19 108
Vincent Massol 21.1 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)
Vincent Massol 15.1 113 {{/code}}
Jean-Vincent Drean 1.19 114
Vincent Massol 15.1 115 === Simple Query with multiple fields ===
116
Vincent Massol 21.1 117 {{code language="none"}}
118 #set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
Jean-Vincent Drean 1.2 119 #foreach ($row in $results)
120 #foreach ($col in $row)
Vincent Massol 21.1 121 #if ($velocityCount == 1)
122 #set($docName = $col)
123 #elseif ($velocityCount == 2)
124 #set($docDate = $col)
Jean-Vincent Drean 1.2 125 #end
126 #end
Vincent Massol 21.1 127 $docName : $docDate <br/>
Jean-Vincent Drean 1.2 128 #end
Vincent Massol 15.1 129 {{/code}}
Jean-Vincent Drean 1.1 130
Vincent Massol 15.1 131 === Getting objects of a specific class ===
132
Vincent Massol 21.1 133 {{code language="none"}}
134 #set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")
135 {{/code}}
Jean-Vincent Drean 1.1 136
Pascal Bastien 35.5 137 === Getting objects properties ===
Vincent Massol 15.1 138
Vincent Massol 21.1 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}}
Jean-Vincent Drean 1.19 142
Pascal Bastien 35.6 143 === Getting documents where objects properties equals some value ===
Vincent Massol 15.1 144
Vincent Massol 21.1 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}}
Jean-Vincent Drean 1.19 148
Marius Dumitru Florea 26.1 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
Pascal Bastien 32.1 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
Pascal Bastien 35.3 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
Vincent Massol 15.1 164 === List users currently editing pages ===
165
Vincent Massol 21.1 166 {{code language="none"}}
167 #set($hql = "select distinct lock.userName from XWikiLock lock")
168 {{/code}}
Jean-Vincent Drean 13.1 169
Vincent Massol 15.1 170 === List attachments of a page ===
171
Vincent Massol 21.1 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}}
Jean-Vincent Drean 14.1 175
Marius Dumitru Florea 23.1 176 === Statistics ===
177
Vincent Massol 32.2 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]]
Marius Dumitru Florea 23.1 184
Vincent Massol 21.1 185 == Non-exhaustive list of queryable Object Fields ==
Flavius Olaru 27.1 186
Martin Jecny 25.1 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).
Jean-Vincent Drean 1.30 188
Vincent Massol 15.1 189 === XWikiDocument ===
190
Vincent Massol 21.1 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##
Jean-Vincent Drean 10.1 194
Vincent Massol 15.1 195 === BaseObject ===
Jean-Vincent Drean 10.1 196
Vincent Massol 21.1 197 * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789##
198 * BaseObject.className : class. Example value: ##XWiki.XWikiUsers##
Jean-Vincent Drean 10.1 199
Vincent Massol 21.1 200 === *Property (StringProperty, IntegerProperty, etc) ===
Jean-Vincent Drean 10.1 201
Vincent Massol 21.1 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##

Get Connected