I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected data. This post is related to this other question (from me too).
So basically I have 3 tables:
default_recipient: stores user_id and message_id for not to repeat messages at default_messages table so when I reply to a message or send multiple users messages user_id and message_id references are stored at this table
default_messages: stores messages fields among sender user_id
default_profiles: stores fields related to users
Since the message is just one for sender and for receiver, then when one of the sides wants to delete and the other does not, then messages can't be physically deleted. So I mark the message with a flag
deleted = 1.
If the same
deleted = 1 in
default_messages and also in
default_recipient, then the message will be deleted physically because both parts sender and receiver deleted the message from their inbox.
Now I have these queries just for testing and see results:
1) select sender_user_id, deleted from default_messages; 2) select * from default_recipient; 3) SELECT DISTINCT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) WHERE msg.sender_user_id = 1 OR rcp.user_id = 1 AND msg.deleted = 0 GROUP BY (p.id) ORDER BY msg.date DESC; 4) SELECT rcp.user_id as rcpuid, rcp.message_id as rcpmid, msg.sender_user_id, msg.id as message_id FROM default_messages msg LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) WHERE (msg.sender_user_id = 1 OR rcp.user_id = 1) AND (msg.deleted = 0 OR rcp.deleted = 0) ORDER BY msg.date DESC;
What I'm trying to do is get all messages from a specific user where the message aren't deleted. So taking as example
user_id = 1, then I need to get those messages where
default_messages.sender_user_id = 1 OR default_recipient.user_id = 1 AND default_messages = 0 AND default_recipient.deleted = 0 meaning: messages for user = 1 where deleted = 0.
Now with my data as you can see here and with queries 3 and 4 the results aren't correct and I'm looking to fix this query.
Any help is appreciated