CODEWITHSUNDEEP
mysqlsql
PWhite
PWhite

Reputation: 141

How to make a particular SELECT query

First of all, I would like to apologize for the vague title of my question. It's very difficult for me to explain the situation, since I'm not an expert in SQL.

I need help in writing a query.

I have a table, called HELLO-MESSAGES, which contains several columns: ID, From, To, Body, Sent (with the data the message has been sent).

I have these rows:

  1. 1, 345xxxxxxx, 339xxxxxxx, "Hey, how are you?", 12/1/2016 17:24
  2. 2, 339xxxxxxx, 345xxxxxxx, "Fine thanks, and you?", 12/1/2016 17:25
  3. 3, 345xxxxxxx, 340xxxxxxx, "The quick...", 12/1/2016 18:24

What I would like to do is to display in a table, all different chats. If my telephone number is 345xxxxxxx, I would like to display a table with the following layout:

<table>
  <tr>
    <td><b>339xxxxxxx</b><br>Fine thanks, and you?</td>
  </tr>
  <tr>
    <td><b>340xxxxxxx</b><br>The quick...</td>
  </tr>
</table>

For example, just like WhatsApp or any other IMs do. I want to display the number of the person I'm chatting to, and the last message sent in that chat. I've a really bad experience in programming queries: this is what I've produced so far:

SELECT * FROM 'HELLO-MESSAGES' WHERE 'From'='345xxxxxxx' OR 'To'='345xxxxxxx'

But it returns EVERY message, as expected!

Please help a beginner that does not like SQL so much :)

EDIT

SELECT *
            FROM (

                         SELECT MAX(d.id) AS max_id
                           FROM (

                                  SELECT df.id
                                    FROM ( SELECT xf.from
                                                , xf.to
                                                , MAX(xf.sent) AS max_date
                                             FROM `HELLO-MESSAGES` xf
                                            WHERE xf.from = '" . $_SESSION['ID'] . "'
                                            GROUP BY xf.from, xf.to
                                         ) mf
                                    JOIN `HELLO-MESSAGES` df
                                      ON df.from = mf.from
                                     AND df.to   = mf.to
                                     AND df.sent = mf.max_date

                                   UNION ALL

                                  SELECT dt.id
                                    FROM ( SELECT xt.from
                                                , xt.to
                                                , MAX(xt.sent) AS max_date
                                             FROM `HELLO-MESSAGES` xt
                                            WHERE xt.to = '" . $_SESSION['ID'] . "'
                                            GROUP BY xt.from, xt.to
                                         ) mt
                                    JOIN `HELLO-MESSAGES` dt
                                      ON dt.from = mt.from
                                     AND dt.to   = mt.to
                                     AND dt.sent = mt.max_date

                                ) q1
                           JOIN `HELLO-MESSAGES` d
                             ON d.id = q1.id
                          GROUP BY IF(d.from<d.to,d.from,d.to),IF(d.from<d.to,d.to,d.from)
                     ) q2
            JOIN `HELLO-MESSAGES` r
                ON r.id = q2.max_id
         ORDER BY r.sent DESC, r.id DESC

Is that right, @spencer7593? Thanks!

Upvotes: 0

Views: 125

Answers (3)

spencer7593
spencer7593

Reputation: 108380

As I indicated in my comments, for a table with a large number of rows, to improve query performance, I would consider implementing an additional "latest message" table to be maintained along with the HELLO-MESSAGES table. But I won't delve into that, since that doesn't address the specific question you were asking. (But I suspect you'll be asking the question how to improve the performance of the query that gets the "latest message"... beyond ensuring appropriate indexes are available, and that table grows to a significant size.)

To get the latest message in each "conversation" sent from a specific a telephone number, you can use a query something like this:

SELECT df.id
     , df.from
     , df.to
     , df.sent
     , df.body
  FROM ( SELECT xf.from
              , xf.to
              , MAX(xf.sent) AS max_date
           FROM `HELLO-MESSAGES` xf
          WHERE xf.from = '345xxxxxxx'
          GROUP BY xf.from, xf.to
       ) mf
  JOIN `HELLO-MESSAGES` df
    ON df.from = mf.from
   AND df.to   = mf.to
   AND df.sent = mf.max_date

If there's more than one message with the same maximum value of "sent" for a given conversation, this query will return all of those rows.

A corresponding query can be used to get the latest message in each "conversation" that is received by a specific telephone number.

SELECT dt.id
     , dt.from
     , dt.to
     , dt.sent
     , dt.body
  FROM ( SELECT xt.from
              , xt.to
              , MAX(xt.sent) AS max_date
           FROM `HELLO-MESSAGES` xt
          WHERE xt.to = '345xxxxxxx'
          GROUP BY xt.to, xt.from
       ) mt
  JOIN `HELLO-MESSAGES` dt
    ON dt.from = mt.from
   AND dt.to   = mt.to
   AND dt.sent = mt.max_date

We can combine the results from those two queries, and return just the unique id value for those messages. We can combine the sets with the UNION ALL operator. (The MySQL optimizer generally doesn't do to well with 'OR' predicates; that's the reason for breaking the query into the two parts.)

SELECT df.id
  FROM ( SELECT xf.from
              , xf.to
              , MAX(xf.sent) AS max_date
           FROM `HELLO-MESSAGES` xf
          WHERE xf.from = '345xxxxxxx'
          GROUP BY xf.from, xf.to
       ) mf
  JOIN `HELLO-MESSAGES` df
    ON df.from = mf.from
   AND df.to   = mf.to
   AND df.sent = mf.max_date

 UNION ALL

SELECT dt.id
  FROM ( SELECT xt.from
              , xt.to
              , MAX(xt.sent) AS max_date
           FROM `HELLO-MESSAGES` xt
          WHERE xt.to = '345xxxxxxx'
          GROUP BY xt.to, xt.from
       ) mt
  JOIN `HELLO-MESSAGES` dt
    ON dt.from = mt.from
   AND dt.to   = mt.to
   AND dt.sent = mt.max_date

The set returned by this query will include the id values of the latest "sent" and "received" message in each conversation.

If we are guaranteed that the id value of a row for a "later" message will be greater than the id value of a row with an "earlier" message, we can take advantage of that, to deal with the issue of more than one "latest" message in the conversation; two or more rows with the same value of Sent.

We can use that to whittle down the results from the previous query to just the latest message in each conversation. We'll rerepresent the result of the previous query as q1 in the next query:

SELECT MAX(d.id) AS max_id
  FROM (
         subquery
       ) q1
  JOIN `HELLO-MESSAGES` d
    ON d.id = q1.id
 GROUP BY IF(d.from<d.to,d.from,d.to), IF(d.from<d.to,d.to,d.from)

That get's the id values of the messages we want to return. We'll represent that result as q2 in the next query.

SELECT r.id
     , r.from
     , r.to
     , r.sent
     , r.body
  FROM (
       ) q2
  JOIN `HELLO-MESSAGES` r
    ON r.id = q2.max_id
 ORDER BY r.sent DESC, r.id DESC

Putting that all together, we get a pretty ugly query:

SELECT r.id
     , r.from
     , r.to
     , r.sent
     , r.body
  FROM (

         SELECT MAX(d.id) AS max_id
           FROM (

                  SELECT df.id
                    FROM ( SELECT xf.from
                                , xf.to
                                , MAX(xf.sent) AS max_date
                             FROM `HELLO-MESSAGES` xf
                            WHERE xf.from = '345xxxxxxx'
                            GROUP BY xf.from, xf.to
                         ) mf
                    JOIN `HELLO-MESSAGES` df
                      ON df.from = mf.from
                     AND df.to   = mf.to
                     AND df.sent = mf.max_date

                   UNION ALL

                  SELECT dt.id
                    FROM ( SELECT xt.from
                                , xt.to
                                , MAX(xt.sent) AS max_date
                             FROM `HELLO-MESSAGES` xt
                            WHERE xt.to = '345xxxxxxx'
                            GROUP BY xt.to, xt.from
                         ) mt
                    JOIN `HELLO-MESSAGES` dt
                      ON dt.from = mt.from
                     AND dt.to   = mt.to
                     AND dt.sent = mt.max_date

                ) q1
           JOIN `HELLO-MESSAGES` d
             ON d.id = q1.id
          GROUP BY IF(d.from<d.to,d.from,d.to),IF(d.from<d.to,d.to,d.from)

       ) q2
  JOIN `HELLO-MESSAGES` r
    ON r.id = q2.max_id
 ORDER BY r.sent DESC, r.id DESC

EDIT: missing qualifier for ambiguous column reference

EDIT: fixed expressions in GROUP BY for q2. (The rows need to be collapsed/grouped by the distinct combined values of from and to. One approach to fixing that is to add an expression to the GROUP BY. That fix is applied to the queries above.

This answer illustrates one approach to returning the specified result. This is not necessarily the best approach. The usage of inline views (derived tables in the MySQL venacular) can impose a (sometimes significant) performance penalty.

Upvotes: 1

itzortzis
itzortzis

Reputation: 45

What you are looking for, I think is not that simple and you need to have good knowledge of sql. I think that the answer is in the following link:

Get Last conversation row from MySQL database table

Hope this helps you. Best regards !

Upvotes: 1

majtenyim
majtenyim

Reputation: 26

You can compare string with LIKE function. In your case:

SELECT * FROM `HELLO-MESSAGES` WHERE `From` LIKE "345%" OR `To` LIKE "345%" ORDER BY `Sent` DESC LIMIT 1;

See more: http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

Upvotes: 0

Related Questions