There is a messageprivate table with the structure:

id | from_user | to_user | message | date | status |

it stores messages. From the structure it is clear that from_user is the user who wrote the message, to_user - to whom he wrote it, message - the message itself, date - when it wrote and status - whether it was read or not, by default this field is 0, and when the user is was written it reads it changes to 1.

It is necessary to write a sql request that will output messages of the grouped type as in a contact when we go into the message section - i.e. we see the list of correspondence with each user, namely the last message from each user and the number of unread messages.

Initially, we know our id - i.e. when we write to someone, our id gets into the from_user field, and when someone writes us, then our id gets into the to_user field.

3 hours already down the drain ...

Thank!

  • select count(1) as count_unreaded from messageprivate where status = 0 and to_user = OUR_USER_IDENTIFICATOR group by from_user ? it would be better to put the test data on sqlfiddle.com - Alexey Shimansky
  • No ... There can be a lot of unread messages from each user, but you only need to display the last from each user and the number of these unread messages ... - Alexander
  • The wording is incomplete. 1) What message to display if the last post was: from the current user? from his interlocutor? 2) The type of fields from_user and to_user is numeric? GUID? text crap? - Akina
  • 1) To deduce exactly the last - if the latter was from the current - it means from the current one and to deduce, if from the interlocutor - it means to deduce from the interlocutor. 2) numeric. I did not understand you further ... - Alexander

2 answers 2

 select if(from_user=@user,to_user,from_user) recipient, max(date) last_date, substr(max(concat(date,message)),20) last_msg, sum(status=0) not_seen from messageprivate where from_user=@user or to_user=@user group by recipient

@user is our user for which sample

Test on sqlfiddle.com

  • went on to learn to write requests . Definitely a plus, with the receipt of the last message slyly, slyly :) - Denis
  • Only status = 0 should be calculated where the message came to our user, i.e. where to_user = @user - Alexander
  • @ Alexander sum(status=0 and to_user=@user) any logical construct in MySQL gives 0 or 1 at the output - Mike
  • Bravo! Thank! - Alexander
  • Not ... Displays not the last message ... date field of type datetime. And I still do not understand what the number 20 in the function substr means, why is it boring there? - Alexander

Not tested on the data, but the algorithm is something like this:

  • we take all sent messages, we make correspondence with people to whom we wrote. We attach the last message to show how in the correspondence in the "My messages" tab

  • do the same thing, only with incoming messages from users

  • we merge everything into one table and sort by time (for convenience, I have the name of the mp table instead of messageprivate )

 select * from ( select to_user, mp1.message, mp1.date, m1.count_sent_unread from /* в подзапросе отправленные сообщения от нас с макс датой и количеством непрочитанных */ (select from_user, to_user, max(date) as max_sent_date, sum(if(status = 0, 1, 0)) as count_send_unread from mp where from_user = 100 group by from_user, to_user ) m1 join /* чтобы получить последнее сообщение в переписке */ mp1 ON m1.from_user = mp1.from_user and m1.to_user = mp1.to_user and m1.max_sent_date = mp1.date UNION /* объединяем то же самое, только со входящими сообщениями */ select from_user, mp2.message, mp2.date, m2.count_receive_unread from /* в подзапросе входящие сообщения с макс датой и количеством непрочитанных */ (select from_user, to_user, max(date) as max_receive_date, sum(if(status = 0, 1, 0)) as count_send_unread from mp where to_user = 100 group by from_user, to_user ) m2 join mp2 ON m2.from_user = mp2.from_user and m2.to_user = mp2.to_user and m2.max_sent_date = mp2.date) mes order by date desc
  • Something is too long ... plus there will be 2 messages from each dialogue. - Akina
  • @Akina would be window functions in MySQL, it would be possible to fit in a couple of lines. - Sergey Gornostaev
  • @Akina my goal is not to write a ready-made optimized script, but to show an idea, oddly enough. I do not understand why 2 messages will fall? Join with max date - by default 1 message. You can, of course, take the id, but not the fact that the table is not a mess. - Denis
  • @SergeyGornostaev Window functions are useless when you need to get the value of one field to the maximum of another - Mike