Expanding HQL query on (appwithinminutes) page property to sort by list value

Help / Discuss
, ,
#1

using Query Module (XWiki.org) I have the following hql query that succesfully returns the (appwithinminutes) pages where the page property ‘Producten’ has the currently viewed page as an entry;

#set ($search= $services.query.xwql("select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj,
DBStringListProperty as prop join prop.list list 
where obj.name=doc.fullName and obj.className='Main.Producten.Componenten.Code.ComponentenClass' and obj.id=prop.id.id and prop.id.name='Producten' and list = '$doc.FullName' "))

I’d like this query to also sort by the value of the (appwithinminutes) pages (static list) property named ‘Type’ , but I’m failing to add this staticlist property of the page to the query.

I’ve attempted (among other things) adding a second

DBStringListProperty as typeprop where obj.className='Main.Producten.Componenten.Code.ComponentenClass' and typeprop.id.name='Type'

But this is giving errors

I also attempted using the XWiki-specific extensions

  • where doc.object(Class).prop = ‘something’
order by doc.Type

but it fails to resolve this Property (I assume because it’s a specific class property)

Any suggestions how to do this in the query?

I think I can do a workaround in velocity but think it’d be cleaner to do the sorting in the query directly.

Thanks in advance!

#2

Can you share those errors with us?

#3

Hey @mleduc , naturally!

#set ($search= $services.query.xwql("select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj,
DBStringListProperty as typeprop where obj.className='Main.Producten.Componenten.Code.ComponentenClass' and typeprop.id.name='Type' ,
DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='Main.Producten.Componenten.Code.ComponentenClass' and obj.id=prop.id.id and prop.id.name='Producten' and list = '$doc.FullName'"))

#foreach($page in $search.execute())
$page
#end

returns

​Failed to execute the [velocity] macro. Cause: [[1,75] Unknown token: ]. Click on this message for details.

HQL velocity error - Pastebin.com

For full clarity, ​the property I’d like to sort by is a Static List named ‘Type’
image

#4

In “select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj,
DBStringListProperty as typeprop where obj.className=‘Main.Producten.Componenten.Code.ComponentenClass’ and typeprop.id.name=‘Type’ ,
DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className=‘Main.Producten.Componenten.Code.ComponentenClass’ and obj.id=prop.id.id and prop.id.name=‘Producten’ and list = ‘Main.Producten.Voorbeeldproduct’”, the part in bold is wrong

Query langue usually expect select ... from .. where .. [...] and keyword expected to appear once.

1 Like
#5

hey @mleduc , thank you for your time and response! (doubly so because your name keeps coming up in many of the support documentations)

I did suspect the double where was where things are going wrong, it’s one of the various attempts/approaches I’ve done to get the second class property in the query.

  • DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='Main.Producten.Componenten.Code.ComponentenClass' and obj.id=prop.id.id and prop.id.name='Producten' and list = '$doc.FullName'"))

ensures that only the right pages from the (appwithinminutes) ComponentenClass are shown (this part works as desired)

The highligted part of the query was (erronously) added as an attempt to properly add a second class property (the static list ‘Type’ property) to the query with the intent that the results can be sorted by what was selected in ‘Type’.

Basically I’m wondering how to sort on Class property (Type) in HQL after having filtered on another class property (Page)

#6

Ended up going with the velocity approach, it seperates the types which works well for the toc and page layout so I’m happy with the result

Thank you for the assistance!

#set ($zoeken= $services.query.xwql("select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='Main.Producten.Componenten.Code.ComponentenClass' and obj.id=prop.id.id and prop.id.name='Producten' and list = '$doc.FullName' "))
#set($typelist=[])
#set($pagelists=[])
#foreach($page in $zoeken.execute())
##init values
#set($pagina = $xwiki.getDocument($page))
#set($type=$pagina.getValue('Type'))
##see if type has been found before
#if($typelist.indexOf($type) == -1)
##new type found
#set ($dummy = 
$typelist.add($type))
#set ($dummy = 
$pagelists.add([$pagina]))
#else
##typelist already exists
#set ($dummy = 
$pagelists[$typelist.indexOf($type)].add($pagina))
#end
##end foreach
#end
##Query done, output the results
#foreach($type in $typelist)
#foreach($pagina in $pagelists[$typelist.indexOf($type)])
##INIT
#set($type=$pagina.getValue('Type'))
#set($merk=$pagina.getValue('Merk'))
#if($merk== '')#set($merk= 'Leeg')#end
#set($urlextern=$pagina.getValue('URL_EXTERN'))
#set($afbeelding=$pagina.getValue('Afbeelding'))
##Header
#if($foreach.count == 1)== $type ==

(% style="width:60%" %)
|=(% style="width:2%" %)# |=(% style="width:30%" %)Afbeelding |=(% style="width:28%" %)Component|=(% style="width:28%" %)Merk
#end
##Row row row of the table
|$foreach.count|#if($afbeelding == '')[[image:attach:Main.Werkwijze.Formulieren.WebHome@letop.gif||data-xwiki-image-style="RB" data-xwiki-image-style-alignment="center"]]##
#else
[[image:attach:$pagina@$afbeelding||data-xwiki-image-style="RB" data-xwiki-image-style-alignment="center"]]##
#end
|[[$pagina.getDisplayTitle()>>$pagina||target="blank_"]]##
#if($urlextern == '')|$merk
#else | [[$merk>>$urlextern||target="blank_"]]
#end
##end foreach
#end
#end
1 Like