Hi,

I am having a difficulty sorting the resultset by count.

Any ideas/solutions would be greatly appreciated.


I've had a look around this forum, google, the hibernate reference docos and some books including "java persistence with hibernate".
So far no luck,


Using Hibernate: v3.0.5
DB: MySQL 4.1

What I need is to return each Category and the number of movies it is applied to.
something like

"select count(mg.genre), g
from MovieGenre mg RIGHT OUTER JOIN mg.genre g
group by g
order by count(mg.genre)
"

What this gives is
- General error, message from server: "Invalid use of group function"
org.hibernate.exception.GenericJDBCException: could not execute query

According to http://www.hibernate.org/hib_docs/refer ... ryhql.html

The following query should be possible:



Also, I cannot do this in SQL or return the entire result set and then sort it, because of the framework and security requirememnts for the project.

I can succesfuly execute the following query:
"select count(mg.genre), g
from MovieGenre mg RIGHT OUTER JOIN mg.genre g
group by g
order by g.name"

but this does not seem to work for count() also
using an alias for a count, which is the SQL way
does not seem to work at all.

-------------------------------------------------------------------------------------------


The setup is this:
==========================================================

CLASSES:
==========================================================
There are three classes

Movie, Genre, and MovieGenre

for simplicity say, (the actual entities are quiet different but the principle is the same)

Code:

class Genre() {
   long UID;
   String name;
}

class MovieGenre () {
   long UID;
   Genre genre;
   Movie movie;      
}

class Movie() {
   long UID;
   String title;
}

* A Movie knows nothing about its Genre(s) and it should not.

* A Movie can be tagged with a Genre. Each time a Movie is tagged an entry is created
in the MovieGenre table.

* A Movie can be tagged with multiple Genres.


TABLES:
==========================================================
For the following tables UID (Unique ID) is the primary key.

For the MovieGenre table assume that the tuple {movie_UID, genre_UID} is unique

Quote:

------------------------------------------------
| MovieGenre |
------------------------------------------------
| UID | movie_UID | genre_UID |
------------------------------------------------
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 4 |
------------------------------------------------



------------------------
| Genre |
------------------------
| UID | name |
------------------------
| 1 | Thriller |
| 2 | Detective |
| 3 | Drama |
| 4 | Horror |
------------------------


--------------------------------
| Movie |
--------------------------------
| UID | title |
--------------------------------
| 1 | movieA |
| 2 | movieB |
| 3 | movieC |
| 4 | movieD |
| 5 | movieE |
--------------------------------

RESULT NEEDED:
==========================================================
What I need is for each Genre to have a count of how many movies are under that Genre

For example, if I execute the following simple SQL query:




The result will be




HIBERNATE MAPPINGS:
==========================================================
I have the following Hibernate mapping classes:
1. MovieGenre.hbm.xml:


2. Genre.hbm.xml:


3. Movie.hbm.xml:

this is interesting, replacing the count(mg.genre)
suggested by the docos with a simple literal like
'col_0_0_' works like a charm because hibernate replaces count(mg.genre) with an alias of 'col_0_0_' as it is the first column selected in the query

This is not particularly elegant but will work as long as hibernate implementation does not change.

Is there a way to use "count(mg.genre) as count" in the same way as SQL alaiases work.

it seems to me that there is something strange going on when hibernate encounters count(mg.genre) and it
does not seem to like the alias count(mg.genre) AS count.

"select count(mg.genre), g
from MovieGenre mg RIGHT OUTER JOIN mg.genre g
group by g
order by 'col_0_0_'
"

does anyone have a better or a "proper" way of doing this?