what im trying to do is get a list of users and there average ratings sorted by the average rating
this is the sql query that does what i need
Code:
select u.name as name, avg(r.score) as avgRating from user as u, rating as r where u.id = r.rateeUserId group by u.name order by avgRating desc
this hql query is as close as ive gotten, all it needs is to be ordered by avg(r.score) which i havent been able to get working
Code:
select u.name, avg(r.score) from User as u, Rating as r where r.ratee.id = u.id group by u.name
ive tried several different variations of the hql to do the ordering such as
Code:
select u.name, avg(r.score) as avgRating from User as u, Rating as r where r.ratee.id = u.id group by u.name order by avgRating
this gives me an error:
Code:
1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.QueryException: , expected in SELECT [select u.name, avg(r.score) as avgRating from com.vivacolombia.model.User as u, com.vivacolombia.model.Rating as r where r.ratee.id = u.id group by u.name order by avgRating]
other hql ive tried
Code:
select u.name, avg(r.score) from User as u join u.receivedRatings as r group by u order by avg(r.score) asc
this generates sql:
Code:
select user0_.name as x0_0_, avg(receivedra1_.score) as x1_0_ from user user0_ inner join rating receivedra1_ on user0_.id=receivedra1_.rateeUserId
group by user0_.id order by avg(receivedra1_.score) asc
almost, but not quite there, what would work would be:
Code:
select user0_.name as x0_0_, avg(receivedra1_.score) as x1_0_ from user user0_ inner join rating receivedra1_ on user0_.id=receivedra1_.rateeUserId
group by user0_.id order by x_1_0_ asc
variations of this hql ive tried also are:
Code:
select u.name, avg(r.score) as avgRating from User as u join u.receivedRatings as r group by u order by avgRating asc
this looks like it would work but seems like "as" doesnt work in the select area of the query, it only works after the from area
so, is there another way to get this information (list of users sorted by average rating)? im all out of ideas, also exhausted all ideas provided by people in irc/#hibernate on freenode, my only option left is using straight jdbc, but i'd rather not if i can use hibernate
original thread:
http://forum.hibernate.org/viewtopic.ph ... 71#2208071my mappings
Code:
<class name="model.Rating" table="rating" proxy="model.Rating">
<id column="id" name="id" type="long" unsaved-value="-1">
<generator class="increment"/>
</id>
<property name="score"/>
<property name="comment"/>
<property name="date" type="timestamp"/>
<many-to-one name="ratee" class="model.User" column="rateeUserId" cascade="save-update"/>
<many-to-one name="rater" class="model.User" column="raterUserId" cascade="save-update"/>
</class>
<class name="model.User" table="user" proxy="model.User">
<id column="id" name="id" type="long" unsaved-value="-1">
<generator class="increment"/>
</id>
<property name="name" type="string"/>
<property name="lastName" type="string"/>
<property name="firstName" type="string"/>
<property name="password" type="string"/>
<property name="dateCreated" type="timestamp"/>
<set name="sentRatings" lazy="true" table="rating" cascade="save-update" order-by="date desc">
<key column="raterUserId"/>
<one-to-many class="model.Rating"/>
</set>
<set name="receivedRatings" lazy="true" table="rating" cascade="save-update" order-by="date desc">
<key column="rateeUserId"/>
<one-to-many class="model.Rating"/>
</set>
</class>
Login
FAQ
Search
