Could be solved if the following issue was solved: https://hibernate.onjira.com/browse/HHH-16 (Explicit joins on unrelated classes)
But this issue has been there for a very long time ... And even with that it would consider that non-matching items (objects without average votes) would return null value for average vote, so I'm not sure sorting would treat these correctly. Jeremie 2012/6/25 Eduard Moraru <enygma2...@gmail.com>: > As far as I can tell, you can only do joins in HQL *if* you have set up > object mappings (one-to-one, one-to-many, etc.). However, as far as I can > see in our Hibernate mapping, XWiki does not do these mappings and only > does basic property-to-primitiveType-to-table-column mappings. > > This is also the cause for the very verbose HQL queries that we have to do: > "... BaseObject obj, StringProperty prop where and prop.id.id=obj.id and > prop.name='age'... etc." instead of "select someObject.someProperty" but > this is also because XWiki objects don`t have individual backing java > classes and that xwiki 'classes' are created at runtime, using a single > generic backing java class (BaseObject). > > Thanks, > Eduard > > On Sun, Jun 24, 2012 at 9:23 AM, ludo...@xwiki.com <ludo...@xwiki.com>wrote: > >> I had the exact same objective a long time ago and could not find a >> solution. I think we thought about creating the missing objects. >> >> I don't think coalesce would have worked. The main issue is get the lines >> >> Ludovic >> >> Envoyé de mon iPad >> >> Le 23 juin 2012 à 21:50, Jeremie BOUSQUET <jeremie.bousq...@gmail.com> a >> écrit : >> >> > 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 <enygma2...@gmail.com>: >> >> 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.bousq...@gmail.com> 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, >> >>> 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.bousq...@gmail.com>: >> >>>> 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@xwiki.org >> >>>> http://lists.xwiki.org/mailman/listinfo/users >> >>> _______________________________________________ >> >>> users mailing list >> >>> users@xwiki.org >> >>> http://lists.xwiki.org/mailman/listinfo/users >> >>> >> >> _______________________________________________ >> >> users mailing list >> >> users@xwiki.org >> >> http://lists.xwiki.org/mailman/listinfo/users >> > _______________________________________________ >> > users mailing list >> > users@xwiki.org >> > http://lists.xwiki.org/mailman/listinfo/users >> _______________________________________________ >> users mailing list >> users@xwiki.org >> http://lists.xwiki.org/mailman/listinfo/users >> > _______________________________________________ > users mailing list > users@xwiki.org > http://lists.xwiki.org/mailman/listinfo/users _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
