Hi,

1.Does anyone know how to recreate a left join query in HQL (the exact syntax using the following setup)?

2. and the necessary entries in the hibernate mapping files for each class
to make it work (using the following setup)?

Any help is greatly appreciated. Thank you in advance.

Using Hibernate: v3,
DB: MySQL 4.1

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

The following HQL equivalent is needed:
SQL: "

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:




My question is what do I need to add to either of those mapping files and how should i formulate my HQL query
to make the qbove SQL equivalent run?

* I would rather not add anything to the Movie file, as the assumption is that movies know nothing about the genres
(unless there is no other way of ahieving the requried result).

==========================================================


TRIED DOING:
==========================================================
I have tried the following HQL query:

"SELECT count(mg.genre) AS count, g
FROM Genre g LEFT JOIN MovieGenre mg
GROUP BY g;"

but hibernate complains that
***** ERROR: path expected for join!
***** ERROR: Invalid path: 'mg.genre'
org.hibernate.hql.ast.QuerySyntaxError: Path expected for join! [SELECT count(mg.genre) AS count, g FROM Genre g LEFT JOIN MovieGenre mg
GROUP BY g ASC]


I know that the following HQL will work but it omits genres which have no entries in the MovieGenre Table so I need
the equivalent of SQL "LEFT JOIN ON"



Any help is greatly appreciated.

Hi olegv,

use left join fetch for left outer join but you cant use with collection.

For all your HQL needs... http://www.hibernate.org/hib_docs/refer ... ryhql.html

Thanks to all who replied. Much appreciated.

I found the answer myself after a good night's sleep.

Here it is for anyone interested.
(I'm sure there is more than one way to solve this)

1. Keep the hibernate mapping files as they are.

That is MovieGenre has the association mappings and Movie and Genre only have simple property mappings.

2. The Query itself was wrong it needs to be changed a bit.
Here is the correct HQL



Here is a short explanation:
We traverse the join path from MovieGenre association where we mapped MovieGenre.genre to the Genre class. Also we do a RIGHT OUTER JOIN because in our HQL MovieGenre is on the left and Genre is on the right. (so to get all the genres even if they do not appear in the MovieGenre Table we must use a RIGHT OUTER JOIN".

By the way for anyone interested there are some really good examples (with explanations) in the "Java Persistence with Hibernate" by Christian Bauer and Gavin King, Manning Publications (2007), Chapter 14 pg 647 - for different types of joins.

Cheers.

Hello
I would like to ask a question about Oracle views's and hibernate (3.2.0cr4),
I know that the materialized views of Oracle can be mapped as table using hibernate, but I ignore if the normal views of Oracle can also be mapped with hibernate.