Wiki source code of velocityHqlExamples
Version 21.1 by Vincent Massol on 2009/09/08
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
18.1 | 1 | {{box cssClass="floatinginfobox" title="**Contents**"}}{{toc start="2" depth="4"/}}{{/box}} |
| |
17.1 | 2 | |
| |
20.1 | 3 | = HQL Query Examples in Velocity = |
 |
1.1 | 4 | |
| |
21.1 | 5 | XWiki allows user to access documents and objects with [[HQL>>http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html]] queries in [[Velocity>>http://jakarta.apache.org/velocity/docs/user-guide.html]] scripts. |
| |
1.1 | 6 | |
| |
21.1 | 7 | General example showing how to display the first 5 results of a given query: |
| |
1.3 | 8 | |
| |
21.1 | 9 | {{code language="none"}} |
| 10 | #set($hql="<query here>") | ||
| 11 | #set($results = $xwiki.searchDocuments($hql, 5, 0)) | ||
| |
1.3 | 12 | #foreach ($item in $results) |
 |
4.5 | 13 | * $item |
| |
1.3 | 14 | #end |
| |
15.1 | 15 | {{/code}} |
| |
1.3 | 16 | |
| |
21.1 | 17 | The examples below will show you various HQL queries that you can write. |
| 18 | |||
| 19 | == Public API (##searchDocuments##) == | ||
| 20 | |||
| 21 | {{velocity}}{{html}} | ||
| 22 | #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.") | ||
| 23 | {{/html}}{{/velocity}} | ||
| 24 | |||
| |
15.1 | 25 | === Simple Query === |
| 26 | |||
| |
21.1 | 27 | Displays all documents who have been created by the user ##XWiki.JohnDoe##: |
| |
1.3 | 28 | |
| |
21.1 | 29 | {{code language="none"}} |
| 30 | #set($hql = "where doc.creator='XWiki.JohnDoe'") | ||
| 31 | {{/code}} | ||
| 32 | |||
| |
15.1 | 33 | === Ordered Query === |
| 34 | |||
| |
21.1 | 35 | Displays all documents who have been created by the user ##XWiki.JohnDoe## and sorted by document's last modification date, in ascending order: |
| |
1.21 | 36 | |
| |
21.1 | 37 | {{code language="none"}} |
| 38 | #set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc") | ||
| 39 | {{/code}} | ||
| 40 | |||
| |
15.1 | 41 | === Advanced Query (date & time) === |
| |
1.35 | 42 | |
| |
21.1 | 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}} | ||
| |
1.21 | 46 | |
| |
21.1 | 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") | ||
| |
15.1 | 49 | {{/code}} |
| |
1.35 | 50 | |
| |
21.1 | 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}} | ||
| |
1.35 | 55 | |
| |
21.1 | 56 | == Privileged API (##search##: Documents, Objects, Properties, etc) == |
| |
1.3 | 57 | |
| |
21.1 | 58 | {{velocity}}{{html}} |
| 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}} | ||
| |
1.1 | 61 | |
| |
15.1 | 62 | === Simple Query === |
| 63 | |||
| |
21.1 | 64 | {{code language="none"}} |
| 65 | #set($hql = "select doc.name from XWikiDocument doc") | ||
| 66 | {{/code}} | ||
| |
1.1 | 67 | |
| |
15.1 | 68 | === Count Query === |
| |
1.19 | 69 | |
| |
21.1 | 70 | {{code language="none"}} |
| 71 | #set($results = $xwiki.search("select count(doc) from XWikiDocument doc")) | ||
| 72 | ## Since $xwiki.search is returning a list, we get its first element | ||
| 73 | Count : $results.get(0) | ||
| |
15.1 | 74 | {{/code}} |
| |
1.19 | 75 | |
| |
15.1 | 76 | === Simple Query with multiple fields === |
| 77 | |||
| |
21.1 | 78 | {{code language="none"}} |
| 79 | #set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0)) | ||
| |
1.2 | 80 | #foreach ($row in $results) |
| 81 | #foreach ($col in $row) | ||
| |
21.1 | 82 | #if ($velocityCount == 1) |
| 83 | #set($docName = $col) | ||
| 84 | #elseif ($velocityCount == 2) | ||
| 85 | #set($docDate = $col) | ||
| |
1.2 | 86 | #end |
| 87 | #end | ||
| |
21.1 | 88 | $docName : $docDate <br/> |
| |
1.2 | 89 | #end |
| |
15.1 | 90 | {{/code}} |
| |
1.1 | 91 | |
| |
15.1 | 92 | === Getting objects of a specific class === |
| 93 | |||
| |
21.1 | 94 | {{code language="none"}} |
| 95 | #set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'") | ||
| 96 | {{/code}} | ||
| |
1.1 | 97 | |
| |
15.1 | 98 | === Getting objects' properties === |
| 99 | |||
| |
21.1 | 100 | {{code language="none"}} |
| 101 | #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'") | ||
| 102 | {{/code}} | ||
| |
1.19 | 103 | |
| |
15.1 | 104 | === Getting documents where objects' properties equals some value === |
| 105 | |||
| |
21.1 | 106 | {{code language="none"}} |
| 107 | #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'") | ||
| 108 | {{/code}} | ||
| |
1.19 | 109 | |
| |
15.1 | 110 | === List users currently editing pages === |
| 111 | |||
| |
21.1 | 112 | {{code language="none"}} |
| 113 | #set($hql = "select distinct lock.userName from XWikiLock lock") | ||
| 114 | {{/code}} | ||
| |
13.1 | 115 | |
| |
15.1 | 116 | === List attachments of a page === |
| 117 | |||
| |
21.1 | 118 | {{code language="none"}} |
| 119 | #set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id") | ||
| 120 | {{/code}} | ||
| |
14.1 | 121 | |
| |
21.1 | 122 | == Non-exhaustive list of queryable Object Fields == |
| |
1.19 | 123 | |
| |
15.1 | 124 | === XWikiDocument === |
| 125 | |||
| |
21.1 | 126 | * **XWikiDocument.fullName** : full name, including space and page name. Example value: ##Main.WebHome## |
| 127 | * XWikiDocument.author : last editor. Example value: ##XWiki.Admin## | ||
| 128 | * XWikiDocument.creator : first editor. Example value: ##XWiki.Admin## | ||
| |
10.1 | 129 | |
| |
15.1 | 130 | === BaseObject === |
| |
10.1 | 131 | |
| |
21.1 | 132 | * **BaseObject.id** : arbitrary unique id of the object. Example value: ##123456789## |
| 133 | * BaseObject.className : class. Example value: ##XWiki.XWikiUsers## | ||
| |
10.1 | 134 | |
| |
21.1 | 135 | === *Property (StringProperty, IntegerProperty, etc) === |
| |
10.1 | 136 | |
| |
21.1 | 137 | * **Property.id.id** : unique id of the object the property belongs to. Example value: ##123456789## |
| 138 | * Property.name : name of the property. Example value: ##first_name## | ||
| 139 | * Property.value : value. Example value: ##John## | ||
| 140 |
