descendente - MySQL ordena mensajes por comentario más reciente O publicado por última vez

order by two columns mysql

Question

¿Cómo puedo ordenar las publicaciones para que la actividad más reciente esté arriba?

# Schema not including all info, including FKs
CREATE TABLE post(
   id int unsigned primary key auto_increment,
   msg text,
   created datetime
)ENGINE=InnoDb;

CREATE TABLE comment(
   id int unsigned primary key auto_increment,
   post_id int unsigned,
   msg text,
   created datetime
)ENGINE=InnoDb;

Deseo ordenar las publicaciones más recientes, donde una nueva publicación es obviamente más reciente que una publicación anterior, pero una publicación anterior que tiene un comentario reciente asociado califica como la más reciente.

Primer intento

# Selecting ''*'' for simplicity in this example
select *
from post p
left join comment c on c.post_id = p.id
group by p.id
order by c.created desc, p.created desc

Esto no funciona porque las publicaciones nuevas se ordenan después de publicaciones antiguas con comentarios.

Segundo intento

select *, if(c.id is null, p.created, c.created) as recency
from post p
left join comment c on c.post_id = p.id
group by p.id
order by recency desc

No funciona porque si una publicación tiene más de un comentario, recencia tendrá el valor creado de la primera fila coincidente, que es el comentario más antiguo.

* ¿Hay alguna manera de agrupar por p.id (de modo que solo se selecciona una copia de cada publicación), pero la ordenación dentro de cada grupo es por c.data desc, pero la orden de consulta por es reciente? No puedo pensar en una manera de hacerlo sin posiblemente agregar un campo actualizado para publicar , en el que escribiría siempre que se publique una respuesta ...

¡Gracias!

Answer 1

Esto debería hacerlo:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(GREATEST(p.created, MAX(c.created)), p.created) DESC

Si suponemos que un comentario siempre es anterior a la publicación, podemos simplificar:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(MAX(c.created), p.created) DESC