22 Jun
2012
22 Jun
'12
11:51 a.m.
Hi,
For the record, the only way I found to achieve my need was to execute
2 requests.
(the idea was to get list of pages ordered by ratings, then date).
First XWQL selects rated pages ordered by ratings then date:
#set($xwql = "select doc.fullName, ratings.averagevote from Document doc,
doc.object(XWiki.AverageRatingsClass) as ratings order by
ratings.averagevote desc, doc.date desc")
Second HQL (XWQL seems not to recognize EXISTS keyword) selects
remaining pages (without ratings) ordered by date:
#set($hql = "select doc.fullName, 0 from XWikiDocument doc where not
exists(select obj.name from BaseObject as obj where
obj.name=doc.fullName and obj.className='XWiki.AverageRatingsClass')
order by doc.date desc")
The concatenation of results of both requests provide the wanted result.
I could not find a solution using a unique request, though, making
difficult (impossible?) to integrate sort by ratings in a livetable,
for example.
BR,
Jeremie
2012/4/27 jerem <jeremie.bousquet(a)gmail.com>om>:
Hello community,
I'm stuck on a problem to query average ratings data from pages - I
installed the Ratings plugin, of course.
The following query works as expected, and shows the pages having average
votes, ordered by ratings then date :
#set($xwql = "select doc.fullName, ratings.averagevote from Document doc,
doc.object(XWiki.AverageRatingsClass) as ratings order by
ratings.averagevote desc, doc.date desc")
Of course, it will return only those pages for wich
XWiki.AverageRatingsClass exists.
Of course, what I would like, is this query to return ALL documents ordered
by ratings, considering no rating equals to value 0.
There are two problems that make this impossible to achieve with XWQL (or
I'd better say that I didn't find how) :
- first, to get all documents I would have to perform a LEFT JOIN. I tried
many syntaxes but could not achieve that. Even in pure JPQL I would not be
able to perform the LEFT JOIN and specify an ON clause anyway ...
- second, I planned to use COALESCE(ratings.averagevote, 0), to consider
that no ratings equal to average 0, but this keyword is not recognized by
XWQL
I realize that this is more a JPQL/Database issue than XWiki issue, but
ordering pages by average ratings is a very common and evident use-case as
long as you activate ratings, so I believe there must be a solution to find
... And sorting things in memory is really not a very attractive solution :(
(adding a XWiki.AverageRatingsClass object to all pages would also do the
trick but it seems really expensive ...)
Thanks,
Jeremie
--
View this message in context:
http://xwiki.475771.n2.nabble.com/XWQL-and-ratings-tp7505249.html
Sent from the XWiki- Users mailing list archive at Nabble.com.
_______________________________________________
users mailing list
users(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users