Simple but complex HQL / SQL query

Question

I have two tables, from one to many relationships (Quizzes, Comments): in Quiz there can be several comments

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

Is it possible to extract abstracts of comments with a single request (if not, is this the best way to do this)? I am now doing a separate request for each quiz to extract the last 5 comments.

(I hope to find one HQL so that I can extract the abstracts of comments)

ps. I am using hibernate / jpa / mysql

Answer 1

I wrote complex SQL that works with MySQL ;-)
basic ideal:

• rownum .
• where,

, date id

: SQL. ( )
SQL ... .

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 where: abs(max_c.LastEntry_id - ranked_c.Rank) < 5

→ :

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 

Answer 2

, . Oracle.

sql :

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;

HQL .

Answer 3

, hql, 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: , , hql, , LIMIT, , SQL. , 100% - SQL, mysql - mysql, , .

EDIT2: SQL, . ( , , ^^). (postgres mysql, ).