Wiki source code of Query Generator

Last modified by etvc developer on 2019/09/19 17:03
Show last authors
1
2 The Query Generator is here to help you write queries and macros allowing to display data from XWiki.
3 It also provides the livetable and query macro to help show results using the query generated and can generate a chart macro.
4
5 * The livetable macro supports a filters that can be included in the macro and also used dynamically.
6 * The query and chart macro support aggregates allowing to calculate numeric fields or count entries for each meta-data.
7
8 {{velocity}}
9 {{html}}
10 #if($request.appname && $request.appname!="")
11 #set($appDoc = $xwiki.getDocument($request.appname))
12 #set($classname = $appDoc.getValue("class"))
13 #elseif($request.classname)
14 #set($classname = $request.classname)
15 #else
16 #set($classname = "XWiki.XWikiUsers")
17 #end
18 #set($list = $services.query.xwql("from doc.object(AppWithinMinutes.LiveTableClass) as obj order by doc.title").execute())
19 <div class="panel panel-default"><div class="panel-heading">Application</div><div class="panel-body">
20 <form action="">
21 Choose Application <select name="appname">
22 <option value="">---</option>>
23 #foreach($appname in $list)
24 <option value="${escapetool.xml($appname)}">${xwiki.getDocument($appname).getDisplayTitle()}</option>
25 #end
26 </select>
27 or Choose Class: <select name="classname">
28 #set($classes = $xwiki.classList)
29 #foreach($classn in $classes)
30 <option value="${classn}" #if($classname==$classn) selected #end>$classn</option>
31 #end
32 </select>
33 <input type="submit" value="Go" class="button" />
34 </form>
35 </div></div>
36 {{/html}}
37
38 #set($classDoc = $xwiki.getDocument($classname).getxWikiClass())
39 #set($qg = $xwiki.parseGroovyFromPage("Macros.QueryGeneratorGroovy"))
40 #set($ok = $qg.setXWiki($xwiki, $context, $services))
41
42 ## Code
43 #set($query = $qg.createQueryFromRequest($classname))
44 #set($class = $xwiki.getDocument($classname).xWikiClass)
45
46 #set($searchdisplayfields = $query.searchdisplayfields)
47 #set($fields = $qg.getPropertyList($classDoc))
48 #if($searchdisplayfields.size()==0)
49 #set($searchdisplayfields = "")
50 #end
51 #set($mydoc = $xwiki.getDocument("Sandbox.Sandbox"))
52 #set($ok = $mydoc.newObject($classname))
53 #set($ok = $mydoc.use($classname))
54 {{html clean=false}}
55 <form action="" method="get">
56 <div class="panel panel-default"><div class="panel-heading">Filters</div><div class="panel-body"><div class="row">
57 <input type="hidden" name="query" value="1" />
58 <input type="hidden" name="classname" value="$classname" />
59 #set($even = true)
60 #set($classes = "col-md-6")
61 #set($fieldList = [])
62 #foreach($field in $fields)
63 #if(!$fieldList.contains($field))
64 <div class="col-sm-6" style="min-height: 50px; padding-bottom: 10px;"><div class="row">
65 <div class="${classes}"><b>${qg.displayPrettyName($field,$mydoc)}</b></div><div class="${classes}">$qg.displaySearch($field, $classname, $query, $fieldList)</div>
66 </div></div>
67 #end
68 #end
69 </div></div></div>
70 <div class="panel panel-default"><div class="panel-heading">Columns</div><div class="panel-body"><div class="row">
71 <div class="col-sm-6" style="min-height: 50px; padding-bottom: 10px;" ><div class="row">
72 <div class="col-md-4"><b>Columns</b></div><div class="col-md-8">$qg.displaySearchColumns($classname, "", $query, $mydoc)</div>
73 </div></div>
74 <div class="col-sm-6" style="min-height: 50px; padding-bottom: 10px;"><div class="row">
75 <div class="col-md-4"><b>Order by</b></div><div class="col-md-8">$qg.displaySearchOrder($classname, "", $query, $mydoc)</div>
76 </div></div>
77 <div class="col-sm-6"><div class="row">
78 <div class="col-md-4"><b>Aggregate Columns</b></div><div class="col-md-8">$qg.displaySearchAggregateColumns($classname, "", $query, $mydoc)</div></div>
79 </div>
80 </div></div></div>
81 <div class="panel panel-default"><div class="panel-heading">Output</div><div class="panel-body"><div class="row">
82 <div class="col-sm-3"><b>Output</b></div>
83 <div class="col-sm-9">
84 #if($request.output)
85 #set($output = $request.getParameterValues("output"))
86 #else
87 #set($output = ["livetable"])
88 #end
89 <input type="checkbox" name="output" value="livetable" #if($output.contains("livetable"))checked#end /> Livetable
90 <input type="checkbox" name="output" value="table" #if($output.contains("table"))checked#end/> Table (XWQL)
91 <input type="checkbox" name="output" value="table-hql" #if($output.contains("table-hql"))checked#end/> Table (HQL)
92 <input type="checkbox" name="output" value="chart" #if($output.contains("chart"))checked#end/> Chart
93 <input type="submit" value="Go" class="button" />
94 </div>
95 </div></div></div>
96 </form>
97
98 #if($request.query)
99 <div class="panel panel-default"><div class="panel-heading">Macros</div><div class="panel-body"><div class="row">
100 #if($request.searchorder)
101 #set($order=$request.searchorder)
102 #set($selectedColumn = $listtool.get($order.split(" "),0))
103 #set($defaultOrder = $listtool.get($order.split(" "),1))
104 #end
105
106 #set($xwlquery = $qg.makeQuery($query))
107 #set($hqlquery = $qg.makeHQLQuery($query))
108 #set($ltFilters = $qg.makeLiveTableFilters($query))
109 #set($searchColumns = [])
110 #foreach($field in $query.searchdisplayfields)
111 #set($ok = $searchColumns.add($field.replaceAll("${classname}_","")))
112 #end
113 #set($columns = $stringtool.join($searchColumns,","))
114 #set($aggColumns = [])
115 #foreach($field in $query.searchaggfields)
116 #set($ok = $aggColumns.add($field.replaceAll("${classname}_","")))
117 #end
118 #set($aggcolumns = $stringtool.join($aggColumns,","))
119 #if($aggColumns&&$aggcolumns!="")
120 #set($queryColumns = "${columns},${aggcolumns}")
121 #else
122 #set($queryColumns = $columns)
123 #end
124 #set($fromAndWhere = $qg.getHQLFromAndWhere($query))
125 ## get chart aggregate
126 #if($aggColumns && $aggColumns.size()==1)
127 #if($aggcolumns=="AGG_COUNT")
128 #set($aggColumn = "doc.fullName")
129 #set($aggFunction = "count")
130 #elseif($aggcolumns=="AGG_COUNTDISTINCT")
131 #set($aggColumn = "distinct doc.fullName")
132 #set($aggFunction = "count")
133 #elseif($aggcolumns.startsWith("AGG_"))
134 #set($aggFunction = $listtool.get($aggcolumns.split("_"),1).toLowerCase())
135 #set($aggColumn = $listtool.get($aggcolumns.split("_"),2))
136 #end
137 #end
138
139 <div class="col-sm-3 col-xs-12" ><b>Query (xwql):</b></div>
140 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">$xwlquery</div>
141 <div class="col-sm-3 col-xs-12"><b>Table Macro (XWQL):</b></div>
142 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">
143 {{query query="$xwlquery" class="$classname" columns="$queryColumns" headers="1" links="0" actions="1" /}}
144 </div>
145 <div class="col-sm-3 col-xs-12"><b>Query (hql):</b></div>
146 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">$hqlquery</div>
147 <div class="col-sm-3 col-xs-12"><b>Table Macro (HQL):</b></div>
148 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">
149 {{query query="$hqlquery" type="hql" class="$classname" columns="$queryColumns" headers="1" links="0" actions="1" /}}
150 </div>
151 <div class="col-sm-3 col-xs-12"><b>Livetable Macro:</b></div>
152 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">
153 {{livetable #if($request.appname)app="${request.appname}"#else className="${classname}" #end columns="${columns}" #if($selectedColumn&&$selectedColumn!="__defaultsorting__")selectedColumn="${selectedColumn}"#end #if($defaultOrder)defaultOrder="${defaultOrder}"#end #if($ltFilters!="")extraParams="${ltFilters}"#end /}}
154 </div>
155 <div class="col-sm-3 col-xs-12"><b>Chart Macro:</b></div>
156 <div class="col-sm-9 col-xs-12" style="padding-bottom: 10px">
157 {{awmchart #if($request.appname)app="${request.appname}"#else className="$classname" #end category="${columns}" type="pie" title="Chart" width="500" height="500" table="1" #if($fromAndWhere.fromQL)fromHQL="${fromAndWhere.fromQL}"#end #if($fromAndWhere.whereQL)whereHQL="${fromAndWhere.whereQL}"#end #if($aggColumn)aggregate="${aggColumn}"#end #if($aggFunction)aggregateFunction="${aggFunction}"#end /}}
158 </div>
159 </div></div></div>
160 {{/html}}
161
162 #if($output.contains("livetable"))
163 (% class="panel panel-default" %)
164 (((
165 (% class="panel-heading" %)((( Livetable Preview )))
166 (% class="panel-body" %)
167 (((
168 #if($aggColumns && $aggColumns.size()>0)
169 {{warning}}Warning: Aggregate columns are not supported by the livetable{{/warning}}
170
171 #end
172
173 {{livetable #if($request.appname)app="${request.appname}"#else className="$classname" #end columns="${columns}" #if($selectedColumn&&$selectedColumn!="__defaultsorting__")selectedColumn="${selectedColumn}"#end #if($defaultOrder)defaultOrder="${defaultOrder}"#end #if($ltFilters!="")extraParams="${ltFilters}"#end /}}
174 )))
175 )))
176 #end
177
178 #if($output.contains("table"))
179 (% class="panel panel-default" %)
180 (((
181 (% class="panel-heading" %)((( Table (XWQL) Preview )))
182 (% class="panel-body" %)
183 (((
184 #if($xcontext.hasRelationalStorageGroupBy)
185 {{warning}}Warning: Multiple select relational storage columns are not supported by XWQL for grouping{{/warning}}
186
187 #end
188
189 #if($xcontext.hasRelationalStorageSort)
190 {{warning}}Warning: Multiple select relational storage columns are not supported by XWQL for sorting{{/warning}}
191
192 #end
193 #if($aggColumns.size()>0 && $searchColumns.size()==0)
194 {{warning}}Warning: At last one column is needed for a query with an aggregate{{/warning}}
195
196 #end
197
198 {{query query="$xwlquery" class="$classname" columns="$queryColumns" headers="1" links="0" actions="1" /}}
199 )))
200 )))
201 #end
202
203 #if($output.contains("table-hql"))
204 (% class="panel panel-default" %)
205 (((
206 (% class="panel-heading" %)((( Table (HQL) Preview )))
207 (% class="panel-body" %)
208 (((
209 #if($aggColumns.size()>0 && $searchColumns.size()==0)
210 {{warning}}Warning: At last one column is needed for a query with an aggregate{{/warning}}
211
212 #end
213
214 {{query query="$hqlquery" type="hql" class="$classname" columns="$queryColumns" headers="1" links="0" actions="1" /}}
215 )))
216 )))
217 #end
218
219 #if($output.contains("chart"))
220 (% class="panel panel-default" %)
221 (((
222 (% class="panel-heading" %)((( Chart Preview )))
223 (% class="panel-body" %)
224 (((
225 #if($searchColumns.size()==0)
226 {{warning}}Warning: At last one column is needed for a chart macro{{/warning}}
227
228 #end
229 #if($searchColumns.size()>1)
230 {{warning}}Warning: Only one column can be used by the chart macro{{/warning}}
231
232 #end
233 #if($aggColumns && $aggColumns.size()>1)
234 {{warning}}Warning: Only one Aggregate column is supported by the chart macro{{/warning}}
235
236 #end
237 {{awmchart #if($request.appname)app="${request.appname}"#else className="$classname" #end category="${columns}" type="pie" title="Chart" width="500" height="500" table="1" #if($fromAndWhere.fromQL)fromHQL="${fromAndWhere.fromQL}"#end #if($fromAndWhere.whereQL)whereHQL="${fromAndWhere.whereQL}"#end #if($aggColumn)aggregate="${aggColumn}"#end #if($aggFunction)aggregateFunction="${aggFunction}"#end /}}
238 )))
239 )))
240 #end
241 #end
242
243 #if($request.debug)
244 (% class="panel panel-default" %)
245 (((
246 (% class="panel-heading" %)((( Debug )))
247 (% class="panel-body" %)
248 (((
249 DEBUG: $qg.getDebug()
250 )))
251 )))
252 #end
253 {{/velocity}}

Tips

You can click on the arrows next to the breadcrumb elements to quickly navigate to sibling and children pages.

Need help?

If you need help with XWiki you can contact: