CODEWITHSUNDEEP
phpmysql
Alen
Alen

Reputation: 917

Conversation-like messaging system

I built messaging system which is something like Facebook messages. Conversations are made in one mysql table, so I have receiverId, senderId, messageContent ... My problem is, I want to implement 'Delete Messages' function but I don't know how to make it, so if one member of that conversation deletes all messages, the other members should still see all messages, until the other members delete those messages as well.

So my approach (obviously wrong one) was to add additional columns: deletedByReceiver and deletedBySender, this works ok for receiver, but if sender opens those messages then he see only messages he sent because my query says: select * from inbox where deletedByReceiver != 1

So I don't ask for code, I ask for idea how to solve this

Upvotes: 0

Views: 785

Answers (4)

Tyson of the Northwest
Tyson of the Northwest

Reputation: 2143

I would use a two table system, one for the messages and one for your inbox. It will increase the size of your database, but it will allow the message to always exist, but the recipient to delete it from their view

Message Table:

msgid* | senderId | messageContent | ts

Inbox table:

id* | msgid_fk | recipientId

To read your messages:

SELECT * FROM messages m JOIN inbox i ON m.msgid = i.msgid_fk WHERE recipientID = '[recipient]'

You can then create a trigger for when the sender deletes a message it also deletes it the recipient inbox. This will also allow multiple recipients receiving the same message while storing the message once.

Upvotes: 0

Clart Tent
Clart Tent

Reputation: 1309

If I understand the problem right, you just need to "OR" the two sets of conditions together. The message needs to be included if either I am the sender and deleteBySender != 1 or I am the receiver and deleteByReceiver!=1.

So something like:

SELECT * FROM inbox 
WHERE 
    (senderId = <MYID> AND deleteBySender!=1) OR 
    (receiverID = <MYID> and deleteByReceiver!=1)

Where, of course, <MYID> is replaced by the viewer's userid.

Upvotes: 2

mb14
mb14

Reputation: 22596

If you want each individual user to be able to delete the message in its own view, seems you need to add create an association table between messages and users, with messageId, userId. A row in the table means this message has been deleted by this user. You can also make this table more generic and have a deleted column (yes/no). That way you could also manage permission etc ...

Upvotes: 0

Friederike
Friederike

Reputation: 1282

You'll need different queries for sender and receiver:

  • for the receiver (as you mentioned) select * from inbox where deletedByReceiver != 1
  • for the sender use select * from inbox where deletedBySender != 1

Upvotes: 0

Related Questions