Last modified by Manuel Leduc on 2023/02/02

<
From version < 14.1 >
edited by Jean-Vincent Drean
on 2008/10/06
To version < 15.1 >
edited by Vincent Massol
on 2009/09/08
>
Change comment: Document converted from syntax xwiki/1.0 to syntax xwiki/2.0

Summary

Details

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

Get Connected