can i use formula for this?

lagcisco · **Posted:** Fri Jul 09, 2004 3:09 am

how can I do something like this but as a formula? is it even possible?

select avg(r.score) from Rating as r where r.ratee = ?

i searched the docs but couldnt find anything too helpful with this

i basically want to get back a list of Users along with the average rating but have them sorted by rating

lagcisco · **Posted:** Fri Jul 09, 2004 4:51 am

oops forgot my mappings, im using mysql 4.0

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>

michael · **Posted:** Fri Jul 09, 2004 4:52 am

I'd suggest you just try it and look at the generated SQL. This might also be doable in plain old HQL.

lagcisco · **Posted:** Fri Jul 09, 2004 5:14 am

this is the sql query that does what I want it to do, still trying to figure its HQL equiv

Code:

select user.name as name, avg(r.score) as avgRating
from user, rating as r 
where user.id = r.rateeUserId 
group by name desc order by avgRating desc limit 10

lagcisco · **Posted:** Fri Jul 09, 2004 5:25 am

in HQL this is as far as ive gotten, almost but not quite there yet

Code:

HQL
select u.name, avg(r.score)  from User as u, Rating as r where r.ratee.id = u.id group by name

so what i tried was to atleast order the results by the avg(r.score), in sql I would use an alias and then order by that alias like avg(r.score) as avgRating but this doesnt work in HQL so im stuck here, anyways ill keep trying different things, but for now ive run out of ideas, oh and i also tried order by avg(r.score) but that didnt work

Code:

HQL
select u.name, avg(r.score)  from User as u, Rating as r where r.ratee.id = u.id group by name

jamstang · **Joined:** Mon May 17, 2004 7:15 am **Posts:** 24

lagcisco wrote:

Code:

HQL
select u.name, avg(r.score)  from User as u, Rating as r where r.ratee.id = u.id group by name

Try:

Code:

    select user, avg(r.score) 
    from User as user join user.receivedRatings as r 
    group by user
    order by avg(r.score) 

lagcisco · **Posted:** Fri Jul 09, 2004 8:36 pm

that didnt work out, any other ideas?

I really dont want to use direct jdbc for this seemingly simple scenario

1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: General error, message from server: "Invalid use of group function"

christian · **Posted:** Fri Jul 09, 2004 8:38 pm

You have to "group by" all properties of user. Please, show the exact code you are executing. We can't help you if we have to guess.

lagcisco · **Posted:** Fri Jul 09, 2004 9:56 pm

i'm using Hibern8IDE 1.0 to test these querys

i tried what jamstang suggested

Code:

 select user, avg(r.score)
    from User as user join user.receivedRatings as r
    group by user
    order by avg(r.score) 

and got this error
1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: General error, message from server: "Invalid use of group function"

I also tried

Code:

 select user.name, avg(r.score)
    from User as user join user.receivedRatings as r
    group by user.name
    order by avg(r.score) 

and got

1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: General error, message from server: "Invalid use of group function"

this is the working SQL that does what I need:

Code:

select user.name as name, avg(r.score) as avgRating
from user, rating as r
where user.id = r.rateeUserId
group by name desc order by avgRating desc limit 10 

Code:

mysql> select user.name as name, avg(r.score) as avgRating
    -> from user, rating as r
    -> where user.id = r.rateeUserId
    -> group by name desc order by avgRating desc limit 10 ;
+----------+-----------+
| name     | avgRating |
+----------+-----------+
| lagcisco |    8.0000 |
| andy3    |    5.7500 |
+----------+-----------+
2 rows in set (0.03 sec)

christian · **Posted:** Fri Jul 09, 2004 10:03 pm

Those queries are OK, please enable the SQL log output in Hibernate and show the translated SQL. I guess that your database doesn't handle it properly.

lagcisco · **Posted:** Tue Jul 13, 2004 1:35 am

here is the output log, i am using mysql 4.0.20a

select user.name, avg(r.score)
from User as user join user.receivedRatings as r
group by user.name
order by avg(r.score)

Hibernate: 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_.name order by avg(receivedra1_.score)
[WARN] JDBCExceptionReporter - SQL Error: 1111, SQLState: S1000
[ERROR] JDBCExceptionReporter - General error, message from server: "Invalid use of group function"
[WARN] JDBCExceptionReporter - SQL Error: 1111, SQLState: S1000
[ERROR] JDBCExceptionReporter - General error, message from server: "Invalid use of group function"
[ERROR] JDBCExceptionReporter - Could not execute query
java.sql.SQLException: General error, message from server: "Invalid use of group function"

select user, avg(r.score)
from User as user join user.receivedRatings as r
group by user
order by avg(r.score)

Hibernate: select user0_.id as id, user0_.name as name, user0_.lastName as lastName, user0_.firstName as firstName, user0_.password as password, user0_.dateCreated as dateCrea6_, user0_.birthDate as birthDate, user0_.sex as sex, user0_.email as email, user0_.addressId as addressId, user0_.id 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)
[WARN] JDBCExceptionReporter - SQL Error: 1111, SQLState: S1000
[ERROR] JDBCExceptionReporter - General error, message from server: "Invalid use of group function"
[WARN] JDBCExceptionReporter - SQL Error: 1111, SQLState: S1000
[ERROR] JDBCExceptionReporter - General error, message from server: "Invalid use of group function"
[ERROR] JDBCExceptionReporter - Could not execute query
java.sql.SQLException: General error, message from server: "Invalid use of group function"

lagcisco · **Posted:** Wed Jul 14, 2004 4:15 pm

can i get some help on this issue? any information I still need to provide to get this working?

anthony · **Posted:** Wed Jul 14, 2004 5:10 pm

what happens when you copy/paste the generated sql into a db client?

lagcisco · **Posted:** Wed Jul 14, 2004 7:29 pm

heres the output

mysql> select user0_.id as id, user0_.name as name, user0_.lastName as lastName, user0_.firstName as firstName, user0_.password as password, user0_.dateCre
ated as dateCrea6_, user0_.birthDate as birthDate, user0_.sex as sex, user0_.email as email, user0_.addres
-> -> ;
ERROR 1109: Unknown table 'user0_' in field list
mysql> select user0_.id as id, user0_.name as name, user0_.lastName as lastName, user0_.firstName as firstName, user0_.password as password,
-> user0_.dateCreated as dateCrea6_, user0_.birthDate as birthDate, user0_.sex as sex, user0_.email as email, user0_.addressId as addressId, user0_.id
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) ;
ERROR 1111: Invalid use of group function
mysql>

lagcisco · **Posted:** Thu Jul 15, 2004 7:37 pm

any ideas?