Wiki source code of velocityHqlExamples

Version 25.1 by MartinJecny on 2010/08/17

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

Get Connected