# 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

Hide last authors

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

When editing in WYSIWYG, type [ and then start typing letters to get suggestions for creating a link.

# Need help?

If you need help with XWiki you can contact:

- Community Support
- Professional Support

XWiki Debian 11.5