[xwiki-users] XWQL and ratings

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.