-1

Private Messaging system displaying users messages concatenated.

• Users sent and received messages grouped together ordered by time_sent
• each message tab will display the sender/receiver's name, message, and time_sent
• any tips on how to improve query speed(such as indexes,etc.)?

MESSAGES TABLE 
--------------
id sender receiver time_sent message opened recipientDelete senderDelete

Users Table
-----------
id first_name last_name

My attempt:

$userid = logged in user
$query="SELECT MAX(id), sender, receiver, MIN(time_sent), message, opened 
        FROM messages 
        WHERE (receiver='$userid' AND recipientDelete='0') 
             OR (sender='$userid' AND senderDelete='0') 
        GROUP BY receiver,sender 
        ORDER BY time_sent DESC LIMIT 8";

RESULT/Problem:
if the logged in user (id=3) receives messages from two users (id=1,and id=2) this query will return messages sent from users 1 and 2

Second attempt:

 $query="SELECT MAX(id), sender, receiver, MIN(time_sent), message, opened      
     FROM ( SELECT 
              CASE WHEN sender = '$userid' THEN receiver ELSE sender END
              MIN(time_sent)
            FROM messages 
            WHERE sender = '$userid' OR receiver = '$userid' 
            GROUP BY CASE WHEN sender = '$userid' THEN receiver ELSE sender END)
         ORDER BY time_sent DESC 
         LIMIT 8";

Returned an error.

Johan
  • 74,508
  • 24
  • 191
  • 319
Bourne
  • 31
  • 2
  • the first query groups the wrong messages together. example if you(jason=user 1) are viewing your inbox the query will group any other user(2,3,4 etc) where you(user 1) are the sender or receiver. So messages sent between users 1-2 and 1-3 will group together as one message when they should be 2 separate messages. – Bourne Sep 02 '11 at 20:50

1 Answers1

0

You're selecting from a derived table. In MySQL, that sub-query must be aliased:

SELECT ...
FROM ( SELECT ...) AS subqueryalias
                   ^^^^^^^^^^^^^^^^
WHERE ...
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • I'm not sure how to perform a sub-query. With the information(table names and rows) above can you provide a solution? if not what other info do you need? – Bourne Sep 02 '11 at 20:41
  • You're already doing a subquery. The FROM (SELECT ...) **IS** the subquery. You should have `... ELSE sender END) AS subqueryalias`. – Marc B Sep 02 '11 at 20:44
  • I tried adding >AS m ORDER BY time_sent DESC LIMIT 8"; but returned an error – Bourne Sep 02 '11 at 21:36