¿Cómo seleccionar los 3 últimos comentarios de los 3 últimos post?

Question

Quisiera saber como hacer para seleccionar los 3 últimos comentarios de los 3 últimos post hechos en dos tablas distintas relacionadas.

Uso Mariadb 10.3

El script para crear la estructura y datos de ejemplo es:

create table posts(
  id int not null auto_increment, 
  primary key (id),
  body text
);

create table coment(
  id int not null auto_increment, 
  primary key (id),
  postID int, 
  foreign key (postID) references posts(id),
  body text
);

insert into posts (body) values 
  ("Post 1"),
  ("Post 2"),
  ("Post 3"),
  ("Post 4");
insert into coment (postID, body) values
  (2, "Segundo222"),
  (2, "Otro comentario"),
  (3, "Holaaaa"),
  (3, "Hola, como estás?"),
  (3, "Bien"),
  (3, ":)"),
  (4, "Prueba");

Ejemplo de como quiero que sea el resultado y esquema de ejemplo: SQL Fiddle

    -- No sé como seleccionar los 3 ultimos comentarios de los 3 ultimos post
    -- El resultado esperado sería:
    -- id | postID | body
    -- 7  | 4      | Prueba
    -- 6  | 3      | :)
    -- 5  | 3      | Bien
    -- 4  | 3      | Hola, como estás?
    -- 2  | 2      | Otro comentario
    -- 1  | 2      | Segundo222
    
    select * from coment order by id desc;

Answer 1

Te explico mi solución:

1. Obtengo en una tabla derivada solamente los últimos 3 posts, valiéndome de la cláusula limit del select anidado, básicamente así:

    select posts.id 
      from posts
     order by posts.id desc
     limit 3
   

2. En otra tabla derivada, obtengo todos los comentarios de las entradas, y para cada una voy numerando los comentarios (1, 2, 3) en orden inverso a su inserción, en el campo CommentReverseNum.

   Para ello me basé en la sección MySQL ROW_NUMBER – adding a row number to each group del tutorial MySQL ROW_NUMBER, This is How You Emulate It (en inglés).

   La consulta de manera independiente luciría algo así:

    set @comment_number := 0;
   
    select   @comment_number :=
               case
                 when @postID = postID then @comment_number + 1
                 else 1
               end as CommentReverseNum
           , @postID := postID postID
           , id
           , body
      from coment
     order by postID, id desc
   

3. Pongo cada una de las consultas anteriores en tablas derivadas dentro de una consulta y hago un inner join entre las mismas. Finalmente, en la cláusula where de esta consulta principal limito para traer solamente los últimos 3 comentarios de cada entrada.

La solución final es:

set @comment_number := 0;

select   orderedComments.id
       , orderedComments.postID
       , orderedComments.body
  from (select posts.id 
          from posts
         order by posts.id desc
         limit 3
       ) lastposts
       inner join (
        select   @comment_number :=
                   case
                     when @postID = postID then @comment_number + 1
                     else 1
                   end as CommentReverseNum
               , @postID := postID postID
               , id
               , body
          from coment
         order by postID, id desc
       ) orderedComments
       on orderedComments.postID = lastposts.id
 where orderedComments.CommentReverseNum <= 3
 order by orderedComments.postID desc, orderedComments.CommentReverseNum;

Que devuelve este resultado:

id      postID  body
-------+-------+---------------------
7       4       Prueba
6       3       :)
5       3       Bien
4       3       Hola, como estás?
2       2       Otro comentario
1       2       Segundo222

La solución quedó también en SQL Fiddle.

Answer 2

Ya que usas MariaDB 10.3x, podemos aprovechar algunas mejoras introducidas que son comunes en otras bases. Hablo particularmente de la función ROW_NUMBER() y las common table expresion. Con esto, la idea será hacer dos numeradores uno por antiguedad del post y otro por la de los post/comment, finalmente deberíamos seleccionar solo aquellos inferiores a 3.

WITH CTE AS (
SELECT c.postID,
       p.body AS 'post_body',
       c.id,
       c.body AS 'comment_body',
       rn_post,
       ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY p.id DESC, c.id DESC) AS rn_coment
       FROM (SELECT *,
                    ROW_NUMBER() OVER (ORDER BY id DESC) AS rn_post
                    FROM posts 
            ) p
              
       INNER JOIN coment c
         on p.id = c. postID
)
SELECT * 
       FROM CTE 
       WHERE rn_coment <= 3 
             AND rn_post <=3 
       ORDER BY rn_post, rn_coment

Puedes consultar este fiddle

Answer 3

(select * from coment where postID =4 order by id desc limit 3)
union
(select * from coment where postID =3  order by id desc limit 3)
union
(select * from coment where postID =2 order by id desc limit 3);

Answer 4

Lo mas simple es proceder de esta forma:

• Selecciona de la tabla posts las columnas deseadas
• Para el caso de los comentarios al no tener una columna de fecha, filtraremos los últimos por el id generado en cada registro de ellos
  • En este caso podemos usar GROUPO_CONCAT para que los 3 últimos comentarios queden en una celda
  • Dentro de la función GROUP_CONCAT indicamos que queremos la columna body de la tabla coment y lo ordenamos de forma descendente para obtener primero los últimos registros hechos
• Como ambas tablas se encuentran relacionadas por el postID entonces podemos hacer un INNER JOIN
• Al final agrupamos los resultados y ordenamos de forma descendente limitando a 3 para obtener las últimas 3 publicaciones

La consulta entonces pudiera lucir así:

SELECT posts.id, 
       posts.body, 
       GROUP_CONCAT(coment.body ORDER BY postID DESC) AS Comentarios
FROM posts 
INNER JOIN coment ON posts.id = coment.postID
GROUP BY posts.id, posts.body
ORDER BY id DESC LIMIT 3;

Y con una salida así: