es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

  • es.davy.ai/
  • Programación /
  • Obteniendo las filas superiores con la fila relacionada más reciente en una tabla unida rápidamente.

Obteniendo las filas superiores con la fila relacionada más reciente en una tabla unida rápidamente.

05 Jun, 2023 Programación

Hay dos tablas, conversations y messages, y quiero obtener las conversaciones junto con el contenido de su último mensaje.

conversations – id (CLAVE PRIMARIA), name, created_at

messages – id, content, createdat, conversationid

Actualmente estamos ejecutando esta consulta para obtener los datos necesarios:


SELECT
conversations.id,
m.content AS last_message_content,
m.created_at AS last_message_at
FROM
conversations
INNER JOIN messages m ON conversations.id = m.conversation_id
AND m.id = (
SELECT
id
FROM
messages _m
WHERE
m.conversation_id = _m.conversation_id
ORDER BY
created_at DESC
LIMIT 1)
ORDER BY
last_message_at DESC
LIMIT 15
OFFSET 0

La consulta anterior devuelve los datos necesarios, pero su rendimiento disminuye a medida que aumenta el número de filas. ¿Existe alguna otra forma de escribir esta consulta con un rendimiento mejorado? Adjunto el ejemplo de Sqlfiddle.

http://sqlfiddle.com/#!17/2decb/2

También se intentaron las sugerencias en una de las respuestas eliminadas:


SELECT DISTINCT ON (c.id)
c.id,
m.content AS last_message_content,
m.created_at AS last_message_at
FROM conversations AS c
INNER JOIN messages AS m
ON c.id = m.conversation_id
ORDER BY c.id, m.created_at DESC
LIMIT 15 OFFSET 0

http://sqlfiddle.com/#!17/2decb/5

Pero el problema con esta consulta es que no se ordena por m.created_at. Quiero que el conjunto de resultados esté ordenado por m.created_at DESC.

Tags:  , , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Respuesta

    Sí, hay una manera mejor de escribir esta consulta con un aumento de rendimiento. Podemos usar la técnica de “greatest-n-per-group” para obtener el último mensaje de cada conversación.

    Aquí está la consulta optimizada:

    SELECT
    conversations.id,
    m.content AS last_message_content,
    m.created_at AS last_message_at
FROM
    conversations
INNER JOIN LATERAL (
    SELECT *
    FROM messages
    WHERE conversation_id = conversations.id
    ORDER BY created_at DESC
    LIMIT 1
) m ON true
ORDER BY
    last_message_at DESC
LIMIT 15
OFFSET 0

    Esta consulta utiliza una subconsulta para obtener el último mensaje de cada conversación utilizando ORDER BY created_at DESC LIMIT 1. Unimos este resultado utilizando un JOIN LATERAL a la tabla conversations para obtener el resultado deseado. Este enfoque es más eficiente y funciona mejor que la consulta anterior.

    Aquí está el sqlfiddle actualizado: http://sqlfiddle.com/#!17/2decb/9

    Esto debería darle el resultado deseado ordenado por m.created_at DESC.

Comments are closed.