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
