[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.
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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
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 < [email protected]> 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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
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 <[email protected]>:
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 < [email protected]> 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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
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 <[email protected]> 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 <[email protected]>:
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 < [email protected]> 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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
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, [email protected] <[email protected]>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 <[email protected]> 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 <[email protected]>:
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 < [email protected]> 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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
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 <[email protected]>:
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, [email protected] <[email protected]>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 <[email protected]> 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 <[email protected]>:
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 < [email protected]> 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 <[email protected]>:
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 [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
participants (4)
-
Eduard Moraru -
jerem -
Jeremie BOUSQUET
-
ludovic＠xwiki.com