Skip to content

Instantly share code, notes, and snippets.

@hassanrazadev

hassanrazadev/latest_conversation.sql

Last active July 4, 2024 03:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hassanrazadev/dbc21e5b97643e211feba23f0b1afe5d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hassanrazadev/dbc21e5b97643e211feba23f0b1afe5d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. hassanrazadev revised this gist Feb 1, 2021. No changes.
  2. hassanrazadev created this gist Feb 1, 2021.
    52 changes: 52 additions & 0 deletions latest_conversation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,52 @@
    SELECT
    *
    FROM
    messages,
    (
    SELECT
    MAX(id) as lastid
    FROM
    messages
    WHERE
    (
    messages.to_id = 1 -- ID to compare with (logged in users's ID)
    OR messages.from_id = 1 -- ID to compare with (logged in users's ID)
    )
    GROUP BY
    CONCAT(
    LEAST(messages.to_id, messages.from_id),
    '.',
    GREATEST(messages.to_id, messages.from_id)
    )
    ) as conversations
    WHERE
    id = conversations.lastid
    ORDER BY
    messages.created_at DESC

    /* Another way using join */

    SELECT
    *
    FROM messages m
    INNER JOIN
    (
    SELECT MAX(id) as lastid
    FROM messages
    WHERE
    (
    messages.to = 1 -- ID to compare with (logged in users's ID)
    OR
    messages.from_id = 1 -- ID to compare with (logged in users's ID)
    )
    GROUP BY
    CONCAT(
    LEAST(messages.to_id, messages.from_id),
    '.',
    GREATEST(messages.to_id, messages.from_id)
    )
    ) conversations
    ON conversations.lastid = m.id

    ORDER BY
    m.created_at DESC