Simple but Complicated HQL/SQL Query

I have two tables, in one to many relationship (Quizzes, Comments): a Quiz can have multiple Comments

I need to display inside a page the last 5 comments for each quiz.

Is it possible to extract theses comments usin开发者_运维问答g one single query (if no, which is the best way to do it)? Right now I am executing a separate query for each quiz to extract the last 5 comments.

(my hope is to find a single HQL to allow me extracting theses comments)

ps. I am using hibernate/jpa/mysql

I wrote a complicated SQL that runs with MySQL ;-)
the basic idee is:

• Order the comments and adding a rownum as rank. comments to same quizz have generated ranks with contiguous nummers
• join a max aggregation with the newest comment per quizz
• with this added information it's possible to build a where clause to limit comments per quizz

Precondition is that a comment with a newer date has a higher id

Update: changed SQL. (realized some missing test cases in my small set of test data)
This part of the SQL is later used twice... you should create a View with that.

SELECT 
  @rownum:=@rownum+1 AS Rank,
  c.*
FROM _comments c, (SELECT @rownum:=0) r
ORDER BY  c.q_id, c.id

-

SELECT 
  ranked_c.*
FROM (
    SELECT 
      @rownum:=@rownum+1 AS Rank,
      c.*
    FROM _comments c, (SELECT @rownum:=0) r
    ORDER BY  c.q_id, c.id
  ) ranked_c
  INNER JOIN (
    SELECT 
      i.q_id,
      MAX(i.Rank) AS LastEntry_id
    FROM  (
      SELECT 
        @rownum:=@rownum+1 AS Rank,
        c.*
      FROM _comments c, (SELECT @rownum:=0) r
      ORDER BY c.q_id, c.id
    ) i
    GROUP BY i.q_id
  ) max_c ON ranked_c.q_id = max_c.q_id
WHERE max_c.LastEntry_id - ranked_c.Rank BETWEEN 0 AND 4 

alternative where clause: abs(max_c.LastEntry_id - ranked_c.Rank) < 5

--> Solution using a View:

CREATE OR REPLACE VIEW V_RankedComments AS (
  SELECT 
    @rownum:=@rownum+1 AS Rank,
    c.*
  FROM _comments c, (SELECT @rownum:=0) r
  ORDER BY c.q_id, c.id
)
SELECT 
  ranked_c.*
FROM V_RankedComments ranked_c
  INNER JOIN (
    SELECT 
      i.q_id,
      MAX(i.Rank) AS LastEntry_id
    FROM V_RankedComments i
    GROUP BY i.q_id
  ) max_c ON ranked_c.q_id = max_c.q_id
WHERE max_c.LastEntry_id - ranked_c.Rank BETWEEN 0 AND 4 

I think you need to use analytic functions to do that. Here is an example for Oracle.

In your cas, the sql query would look like this :

SELECT quizz_id, comment_id, comment_text FROM (
    SELECT c.quizz_id, c.comment_id, c.comment_text, ROW_NUMBER()
    OVER (PARTITION BY c.quizz_id ORDER BY c.date DESC) AS rn
    FROM comments c)
WHERE rn <= 5 order by quizz_id, rn;

But You won't be able to use HQL for this kind of query.

I don't think this is possible in hql, but this is my attempt using standard sql:

SELECT
  q.id AS quiz_id, c.id AS comment_id, c.text AS comment_text, c.date AS comment_date
FROM
  quiz q
JOIN
  comments c
ON
  q.id = c.quiz_id
WHERE
  c.id IN
  (SELECT 
     id
   FROM
     comments c2
   WHERE
     c2.quiz_id = q.id
   ORDER BY
     date desc
   LIMIT 5
  )
ORDER BY
  q.id ASC, c.date ASC

EDIT: The reason why I think this won't be possible in hql is because it does not support LIMIT as far as I know, because it is not standard SQL. This also means that my example is not 100% pure standard SQL, but mysql supports it - and you use mysql, so I think that's ok.

EDIT2: Fixed the SQL, because it was wrong. Now it uses a subselect (I'm not sure if this is fast anymore, but I rely on the query optimizer there ^^). Also tested it on a local database (postgres instead of mysql, but it should work in both).