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 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.
8
9 == Public API (##searchDocuments##) ==
10
11 {{velocity}}
12 {{html}}
13 #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.")
14 {{/html}}
15 {{/velocity}}
16
17 General example showing how to display the first 5 results of a given query:
18
19 {{code language="none"}}
20 #set($hql = "<query here>")
21 #set($results = $xwiki.searchDocuments($hql, 5, 0))
22 #foreach ($item in $results)
23 * $item
24 #end
25 {{/code}}
26
27 The examples below will show you various HQL queries that you can write.
28
29 === Simple Query ===
30
31 Displays all documents who have been created by the user ##XWiki.JohnDoe##:
32
33 {{code language="none"}}
34 #set($hql = "where doc.creator='XWiki.JohnDoe'")
35 {{/code}}
36
37 === Ordered Query ===
38
39 Displays all documents who have been created by the user ##XWiki.JohnDoe## and sorted by document's last modification date, in ascending order:
40
41 {{code language="none"}}
42 #set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc")
43 {{/code}}
44
45 === Advanced Query (date & time) ===
46
47 {{velocity}}
48 {{html wiki="true"}}
49 #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.")
50 {{/html}}
51 {{/velocity}}
52
53 {{code language="none"}}
54 #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")
55 {{/code}}
56
57 Other examples:
58
59 * 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}}
60 * 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}}
61 * 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}}
62 * Comparing two dates: {{code language="none"}}where ((year(date1) > year(date2) OR (year(date1) = year(date2) AND month(date1) > month(date2)) OR (year(date1) = year(date2) AND month(date1) = month(date2) AND day(date1) >= day(date2)){{/code}}
63
64 == Privileged API (##search##) ==
65
66 {{velocity}}
67 {{html wiki="true"}}
68 #warning("Calls to te 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.")
69 {{/html}}
70 {{/velocity}}
71
72 General example showing how to display the first 5 results of a given query:
73
74 {{code language="none"}}
75 #set($hql = "<query here>")
76 #set($results = $xwiki.search($hql, 5, 0))
77 #foreach ($item in $results)
78 * $item
79 #end
80 {{/code}}
81
82 The examples below will show you various HQL queries that you can write.
83
84 === Simple Query ===
85
86 {{code language="none"}}
87 #set($hql = "select doc.name from XWikiDocument doc")
88 {{/code}}
89
90 === Count Query ===
91
92 {{code language="none"}}
93 #set($results = $xwiki.search("select count(doc) from XWikiDocument doc"))
94 ## Since $xwiki.search is returning a list, we get its first element
95 Count : $results.get(0)
96 {{/code}}
97
98 === Simple Query with multiple fields ===
99
100 {{code language="none"}}
101 #set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
102 #foreach ($row in $results)
103 #foreach ($col in $row)
104 #if ($velocityCount == 1)
105 #set($docName = $col)
106 #elseif ($velocityCount == 2)
107 #set($docDate = $col)
108 #end
109 #end
110 $docName : $docDate <br/>
111 #end
112 {{/code}}
113
114 === Getting objects of a specific class ===
115
116 {{code language="none"}}
117 #set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")
118 {{/code}}
119
120 === Getting objects' properties ===
121
122 {{code language="none"}}
123 #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'")
124 {{/code}}
125
126 === Getting documents where objects' properties equals some value ===
127
128 {{code language="none"}}
129 #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'")
130 {{/code}}
131
132 {{code language="none"}}
133 #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")
134 {{/code}}
135
136 === List users currently editing pages ===
137
138 {{code language="none"}}
139 #set($hql = "select distinct lock.userName from XWikiLock lock")
140 {{/code}}
141
142 === List attachments of a page ===
143
144 {{code language="none"}}
145 #set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
146 {{/code}}
147
148 === Statistics ===
149
150 * [[Most Viewed Articles Snippet>>extensions:Extension.Most Viewed Articles]]
151 * [[Most Active Contributors In Group Snippet>>extensions:Extension.Most Active Contributors In Group]]
152 * [[Number Of Active Users Per Day And Per Week Snippet>>extensions:Extension.Number Of Active Users Per Day And Per Week]]
153 * [[Number Of Edited Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Edited Articles Per Day And Per Week]]
154 * [[Number Of Created Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Created Articles Per Day And Per Week]]
155 * [[Number Of Deleted Articles Per Day And Per Week Snippet>>extensions:Extension.Number Of Deleted Articles Per Day And Per Week]]
156
157 == Non-exhaustive list of queryable Object Fields ==
158
159 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).
160
161 === XWikiDocument ===
162
163 * **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome##
164 * XWikiDocument.author : last editor. Example value: ##XWiki.Admin##
165 * XWikiDocument.creator : first editor. Example value: ##XWiki.Admin##
166
167 === BaseObject ===
168
169 * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789##
170 * BaseObject.className : class. Example value: ##XWiki.XWikiUsers##
171
172 === *Property (StringProperty, IntegerProperty, etc) ===
173
174 * **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789##
175 * Property.name : name of the property. Example value: ##first_name##
176 * Property.value : value. Example value: ##John##

Get Connected