Sort by appwithinminutes date field

Help / Discuss
, ,
#1

Hey everyone

I have a xwql query that returns a bunch of page titles which I can then put in a table based on what pages were entered on the relevant class pages

#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.Werkwijze.Hardware en Servers automatisering.Logboek automatisering.Code.Logboek automatiseringClass' and list = '$doc.FullName' "))

#foreach ($page in $zoeken.execute())
        #set($pagina = $xwiki.getDocument($page))

One of the other appwithinminute variables of the Class is a ‘done on’ date value ( $pagina.getValue(‘Datum’) )

What i’m trying to do is sort the resultant pages of the xwql query by this Date value.

  • I attempted to sort by in the query directly 'Order by ‘$doc.Datum’ but it gives me this error:
Failed to execute the [velocity] macro. Cause: [[1,450] expecting: abs, concat, count, length, locate, lower, mod, size, sqrt, substring, trim, upper, integer literal, aggregate func, functions returning datetime, id]. Click on this message for details.

When the query is:

#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.Werkwijze.Hardware en Servers automatisering.Logboek automatisering.Code.Logboek automatiseringClass' and obj.id=prop.id.id and prop.id.name='Onderdeel' and list = '$doc.FullName' order by '$doc.Datum' "))
  • Using both the custom Datum value and $doc.creationDate result in the same error.

I’ve looked at the CollectionTool sort function but even when having a list of ordered dates id still need to find the corresponding page objects with the dates… which doesnt seem like the right way to do this.

I’m hoping someone here has an idea and can send me in the right direction,

Thank you in advance!

#2

Hi @Wardenburg,

You are using HQL syntax with $services.query.xwql.

1 Like
#3

Hey @acotiuga , thanks a ton!

That sorts the error I was getting on the order by!

It’s working now on creationDate:

#set ($zoeken = $services.query.hql("
select doc.fullName, 
doc.creationDate 

from XWikiDocument as doc, 
BaseObject as obj, 
DBStringListProperty as prop join prop.list list 

where obj.name=doc.fullName 
and obj.className='Main.Werkwijze.Hardware en Servers automatisering.Logboek automatisering.Code.Logboek automatiseringClass' 
and obj.id=prop.id.id and prop.id.name='Onderdeel' 
and list = '$doc.FullName' 

order by doc.creationDate"))

However despite some attempts i’m not clear how I build the query to order by the Datum property of the collected pages, I suspect it’s held in the prop.id.id somewhere but can’t seem to find it!

Definetly a step forward and learning things here though so appreciate it!

#4

If is in the same object, you should try obj.id = prop2.id.id and prop2.id.name = 'Datum' order by prop2.value.

#5

Hey @acotiuga , thanks for the suggestion! Unfortunately this gives an error:

Failed to execute the [velocity] macro. Cause: [Invalid path: 'prop2.id.id'

When I do:

#set ($zoeken = $services.query.hql("
select doc.fullName, 
doc.creationDate 

from XWikiDocument as doc, 
BaseObject as obj, 
DBStringListProperty as prop 

join prop.list list 

where (obj.name=doc.fullName 
and obj.className='Main.Werkwijze.Hardware en Servers automatisering.Logboek automatisering.Code.Logboek automatiseringClass' 
and obj.id=prop.id.id and prop.id.name='Onderdeel' 
and list = '$doc.FullName')

and obj.id = prop2.id.id and prop2.id.name = 'Datum'

order by prop2.value

"))

I think it’s because the prop variable itself is declared near the from section of the query using DBStringListProperty (XWiki Platform - Old Core 5.1 API)

DBStringListProperty as prop

I attempted to add a second prop to the from section but this didnt even run (I was worried it’d crashs omething in the background for a moment haha)

from XWikiDocument as doc, 
BaseObject as obj, 
DBStringListProperty as prop ,
DBStringListProperty as prop2

So the search continues!

#6

Isn’t your Datum property of type Date?

#7

Hey @acotiuga it sure is! See the relevant entries in the class:

image

image

#8

Then you missed the DateProperty as prop2

1 Like
#9

Ah thanks a ton @acotiuga , The following code does what I was looking for now:

#set ($zoeken = $services.query.hql("
select doc.fullName

from XWikiDocument as doc, 
BaseObject as obj, 
DateProperty as prop2,
DBStringListProperty as prop join prop.list list 

where obj.name=doc.fullName 
and obj.className='Main.Werkwijze.Hardware en Servers automatisering.Logboek automatisering.Code.Logboek automatiseringClass' 
and obj.id=prop.id.id and prop.id.name='Onderdeel' 
and list = '$doc.FullName' 
and obj.id = prop2.id.id and prop2.id.name = 'Datum'

order by prop2.value DESC
"))
#set($pagelists=[])
#if ($zoeken.execute().size() == 0)
    ## nulcase
#else
    #foreach ($page in $zoeken.execute())
        #set($pagina = $xwiki.getDocument($page))
        #dothings
    #end
#end

I think I’m not fully clicking with the hql queries just yet, even with the HQL Scripting Examples in Velocity (XWiki.org). I just now found the Non-exhaustive list of queryable Object Fields which helps. And with your help today I’ve also learned more about how to build the queries (and that it was in fact, hql format :sweat_smile:)

Wishing you the best of weekends from the Netherlands @acotiuga

and thanks again!

1 Like