Re: [xwiki-users] XWQL and ratings

Hi Eduard, Well, that's what I expected but I did not succeed to find a solution with a unique query, wether in XWQL or HQL. The solution would be to do a left join, but I could not find how to formulate it and I wonder if it's possible with the current mappings. Jeremie 2012/6/23 Eduard Moraru &lt;enygma2002(a)gmail.com&gt;om>: > Hi Jeremie, > > If you ultimately resorted to HQL, I guess you could of used HQL all the > way instead of XWQL and have only 1 query instead of XWQL+HQL, right? > > Thanks, > Eduard > > On Fri, Jun 22, 2012 at 2:51 PM, Jeremie BOUSQUET < > jeremie.bousquet(a)gmail.com&gt; wrote: > >> 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.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

>> 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 &lt;jeremie.bousquet(a)gmail.com&gt;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

>>> votes, ordered by ratings then date : >>> >>> #set($xwql = "select doc.fullName, ratings.averagevote from Document

>>> 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

>>> 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

>> be >>> able to perform the LEFT JOIN and specify an ON clause anyway ... >>> - second, I planned to use COALESCE(ratings.averagevote, 0), to

>>> that no ratings equal to average 0, but this keyword is not

>>> XWQL >>> >>> I realize that this is more a JPQL/Database issue than XWiki issue,

>>> ordering pages by average ratings is a very common and evident

>> 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

_______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users