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}}

Need help?

If you need help with XWiki you can contact: