Wiki source code of velocityHqlExamples
Version 17.1 by Vincent Massol on 2009/09/08
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
17.1 | 1 | {{box cssClass="floatinginfobox" title="**Contents**"}}{{toc start="2" depth="3"/}}{{/box}} |
| 2 | |||
| |
15.1 | 3 | {{include document="DevGuide.velocityHqlExamplesMacro"/}} |
 |
1.36 | 4 | |
| |
15.1 | 5 | = Velocity HQL query examples = |
| |
1.1 | 6 | |
| |
15.1 | 7 | 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 | 8 | |
| |
15.1 | 9 | {{toc start="" depth="" numbered=""/}} |
| |
1.31 | 10 | |
| |
15.1 | 11 | == Public API (searchDocuments) == |
| |
1.3 | 12 | |
| |
15.1 | 13 | {{velocity filter="none"}} |
| 14 | {{html clean="false" wiki="true"}} | ||
| |
1.6 | 15 | #info("With this API the query consist in the WHERE condition.\\ |
 |
4.4 | 16 | Any user with edit rights can use this API (as in write a script using it).\\ |
| 17 | Any user with view rights can view the result of such a query.") | ||
| |
15.1 | 18 | <p/> |
| 19 | You can execute queries as follows: | ||
| |
1.3 | 20 | |
| |
15.1 | 21 | {{code}} |
| |
1.22 | 22 | #set($query="where doc.creator='XWiki.VincentMassol'") |
| |
1.25 | 23 | #set($results = $xwiki.searchDocuments($query, 5, 0)) |
| |
1.3 | 24 | #foreach ($item in $results) |
| |
4.5 | 25 | * $item |
| |
1.3 | 26 | #end |
| |
15.1 | 27 | {{/code}} |
| |
1.3 | 28 | |
| |
15.1 | 29 | === Simple Query === |
| 30 | |||
| |
1.22 | 31 | #set($hql="where doc.creator='XWiki.VincentMassol'") |
| |
1.3 | 32 | #displayQuery($hql false) |
| 33 | |||
| |
15.1 | 34 | === Ordered Query === |
| 35 | |||
| |
1.22 | 36 | #set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc") |
| |
1.21 | 37 | #displayQuery($hql false) |
| 38 | |||
| |
15.1 | 39 | === Advanced Query (date & time) === |
| |
1.35 | 40 | |
| |
15.1 | 41 | 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. |
| 42 | <p/> | ||
| |
1.35 | 43 | #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") |
| |
1.21 | 44 | #displayQuery($hql false) |
| 45 | |||
| |
15.1 | 46 | {{code}} |
| |
1.35 | 47 | Other examples, documents modified : |
| |
1.21 | 48 | |
| |
1.35 | 49 | during current day : "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" |
| 50 | during current week : "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" | ||
| 51 | during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc" | ||
| |
15.1 | 52 | {{/code}} |
| |
1.35 | 53 | |
| |
15.1 | 54 | == Privileged API (search : Documents, Objects, Properties, etc) == |
| |
1.35 | 55 | |
| |
1.6 | 56 | #warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\ |
| |
4.4 | 57 | The reason is that search can be used to send any HQL command like update, delete, etc.") |
| |
15.1 | 58 | <p/> |
| 59 | You can execute queries as follows: | ||
| |
1.3 | 60 | |
| |
15.1 | 61 | {{code}} |
| |
1.2 | 62 | #set($query="select doc.name from XWikiDocument doc") |
| |
1.25 | 63 | #set($results = $xwiki.search($query, 5, 0)) |
| |
1.2 | 64 | #foreach ($item in $results) |
| 65 | * $item <br/> | ||
| 66 | #end | ||
| |
15.1 | 67 | {{/code}} |
| |
1.1 | 68 | |
| |
15.1 | 69 | === Simple Query === |
| 70 | |||
| |
1.1 | 71 | #set($hql="select doc.name from XWikiDocument doc") |
| |
1.3 | 72 | #displayQuery($hql true) |
| |
1.1 | 73 | |
| |
15.1 | 74 | === Count Query === |
| |
1.19 | 75 | |
| |
15.1 | 76 | {{code}} |
| |
1.19 | 77 | #set($query="select count(doc) from XWikiDocument doc") |
| |
1.20 | 78 | #set($results = $xwiki.search($query)) |
| |
1.21 | 79 | ## $xwiki.search returning a list, we get its first element |
| |
1.19 | 80 | $query result : $results.get(0) |
| |
15.1 | 81 | {{/code}} |
| |
1.19 | 82 | |
| 83 | #set($query="select count(doc) from XWikiDocument doc") | ||
| |
1.20 | 84 | #set($results = $xwiki.search($query)) |
| 85 | $query results : $results.get(0) | ||
| |
15.1 | 86 | <p/> |
| |
1.1 | 87 | <br/> |
| |
1.6 | 88 | |
| |
15.1 | 89 | === Simple Query with multiple fields === |
| 90 | |||
| 91 | {{code}} | ||
| |
1.25 | 92 | #set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0)) |
| |
1.2 | 93 | #foreach ($row in $results) |
| 94 | #foreach ($col in $row) | ||
| 95 | #if ($velocityCount==1) | ||
| 96 | #set($docName=$col) | ||
| 97 | #elseif ($velocityCount==2) | ||
| 98 | #set($docDate=$col) | ||
| 99 | #end | ||
| 100 | #end | ||
| 101 | $docName : $docDate <br/> | ||
| 102 | #end | ||
| |
15.1 | 103 | {{/code}} |
| |
1.1 | 104 | |
| 105 | #set($hql="select doc.name, doc.date from XWikiDocument doc") | ||
| |
1.3 | 106 | #displayQuery($hql true) |
| |
15.1 | 107 | <p/> |
| 108 | <br/> | ||
| |
1.1 | 109 | |
| |
15.1 | 110 | === Getting objects of a specific class === |
| 111 | |||
| |
1.1 | 112 | #set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'") |
| |
1.3 | 113 | #displayQuery($hql true) |
| |
15.1 | 114 | <p/> |
| 115 | <br/> | ||
| |
1.1 | 116 | |
| |
15.1 | 117 | === Getting objects' properties === |
| 118 | |||
| |
1.1 | 119 | #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'") |
| |
1.3 | 120 | #displayQuery($hql true) |
| |
15.1 | 121 | <p/> |
| 122 | <br/> | ||
| |
1.19 | 123 | |
| |
15.1 | 124 | === Getting documents where objects' properties equals some value === |
| 125 | |||
| |
1.30 | 126 | #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'") |
| |
1.19 | 127 | #displayQuery($hql true) |
| 128 | |||
| |
15.1 | 129 | === List users currently editing pages === |
| 130 | |||
| |
13.1 | 131 | #set($hql="select distinct lock.userName from XWikiLock lock") |
| 132 | #displayQuery($hql true) | ||
| 133 | |||
| |
15.1 | 134 | === List attachments of a page === |
| 135 | |||
| |
14.1 | 136 | #set($hql="select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id") |
| 137 | #displayQuery($hql true) | ||
| 138 | |||
| |
15.1 | 139 | == Non-exhaustive list of queryable object fields == |
| |
1.19 | 140 | |
| |
15.1 | 141 | #macro(exval $value)Example of value : //$value//#end |
| |
1.30 | 142 | |
| |
10.1 | 143 | |
| |
15.1 | 144 | === XWikiDocument === |
| 145 | |||
| 146 | * **XWikiDocument.fullName** : full name, including space and page name. #exval("Main.WebHome"). | ||
| |
10.1 | 147 | * XWikiDocument.author : last editor. #exval("XWiki.Admin"). |
| 148 | * XWikiDocument.creator : first editor. #exval("XWiki.Admin"). | ||
| 149 | |||
| |
15.1 | 150 | === BaseObject === |
| |
10.1 | 151 | |
| |
15.1 | 152 | * **BaseObject.id** : arbitrary unique id of the object. #exval("123456789"). |
| |
10.1 | 153 | * BaseObject.className : class. #exval("XWiki.XWikiUsers"). |
| 154 | |||
| |
15.1 | 155 | === *Property (StringProperty, etc) === |
| |
10.1 | 156 | |
| |
15.1 | 157 | * **Property.id.id** : unique id of the object the property belongs to. #exval("123456789"). |
| |
10.1 | 158 | * Property.name : name of the property. #exval("first_name"). |
| 159 | * Property.value : value. #exval("John"). | ||
| |
15.1 | 160 | {{/html}} |
| 161 | {{/velocity}} |
