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