mysql query for getting all messages that belong to user's contacts
So I have a database that is setup sort of like this (simplified, and in terms of the tables, all are InnoDBs):
Users: contains based user authentication information (uid, usern开发者_开发百科ame, encrypted password, et cetera)
Contacts: contains two rows per relationship that exists between users as
(uid1, uid2), (uid2, uid1) to allow for a good 1:1 relationship
(must be mutual) between users
Messages: has messages that consist of a blob, owner-id, message-id (auto_increment)
So my question is, what's the best MySQL query to get all messages that belong to all the contacts of a specific user? Is there an efficient way to do this?
select m.owner-id, m.blob
from Users u
join Contacts c on u.uid = c.uid1
join Messages m on m.owner-id = c.uid2
where u.username = 'the_username';
Now the thing is here that this will return every message owned by all of the contacts regardless of whether the message was associated with some interaction between uid1 and uid2.
Also if you want to see the contact names next to the messages instead of a uid:
select u2.username, m.blob
from Users u
join Contacts c on u.uid = c.uid1
join Messages m on m.owner-id = c.uid2
join Users u2 on u2.uid = c.uid2
where u.username = 'the_username';
hmm after re-reading your question - I noticed that "must be mutual thing". That sounds like you would also need an exists query to check on that part to restrict the results to only mutual relationships.
Might be easier to write if you provided sample table definitions.
精彩评论