CODEWITHSUNDEEP
sqlhibernatehql
Will Barnes
Will Barnes

Reputation: 1

How to write query of queries in HQL (hibernate)

I'm trying to SUM some data via query of queries. Its a fairly complicated sql query over complicated relationships that I'd like to translate into HQL.

I'll use a simplified version of the data relationships to make discussion easier.

So how could I translate this into HQL? Is query of queries even possible in HQL?

Example:

Suppose we have a Movie Critic that reviews movies online, and we want to return totals for the number of movies he's reviewed, the number of movies he loved, & the number of movies he hates.

Tables:

SQL Query:

(this is a made up scenario, the solution i'm working on is for facility management. I wrote this query on stack overflow, so there very well could be flaws in it.)

SELECT criticSummary.id , COUNT(criticSummary.reviewId) as totalReviews , SUM(criticSummary.isLoved) as totalLoved , SUM(criticSummary.isHated) as totalHated

FROM (

SELECT DISTINCT critic.id AS id, 
    review.id AS reviewId, 
    review.isLoved AS isLoved, 
    CASE WHEN review.isLoved = 1 THEN 0 ELSE 1 END AS isHated

FROM [critic] critic

INNER JOIN [review] review
ON (
    review.criticId = critic.id
    AND review.active = 1
)

WHERE critic.active = 1

) AS criticSummary

GROUP BY criticSummary.id

Upvotes: 0

Views: 811

Answers (1)

Alan Hay
Alan Hay

Reputation: 23246

Do you really need to use HQL? You have some options to simplify things:

1 Hibernate Formula Fields in Critic Entity

@Formula("select count(*) from reviews where id = id and loved = 1")
public int totalMoviesLoved;

@Formula("select count(*) from reviews where id = id and loved = 0")
public int totalMoviesHated;

public int getTotalMoviesReviewed(){
    return get totalMoviesHated + totalMoviesLoved;
}

2 Create a Database View

Create a Database view, say critic_summary_data Create an entity mapped to this view (works just the same as a table)

Map this in Critic Entity

@OneToOne
private CriticSummaryData summaryData;

Upvotes: 0

Related Questions