# Wiki source code of Query Generator

Last modified by etvc developer on 2019/09/19 17:03
Export
Office Formats
Export as PDF
Other Formats
Export as HTML

Show last authors

author | version | line-number | content
--- | --- | --- | ---
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}}

# Applications

# 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:

- Community Support
- Professional Support

XWiki Debian 11.5