Reputation: 275
I'm trying to build a user based message system. I'd like messages to show up that are sent between friends, sort of like a facebook news feed.
I'm using a query:
SELECT friend_user_2, user_id, user_name, sent_id, sent_from_id, sent_to_id
FROM friends, users, sent_messages
WHERE (user_id = sent_from_id AND friend_user_1 = '$user_id' AND sent_from_id = friend_user_2)
OR (friend_user_1='$user_id' AND friend_user_2=sent_to_id AND user_id=sent_to_id) ORDER BY sent_id
The problem im running into, is that the user_id of the sender and receiver aren't identified separately, it only returns the user_id of the friend. I would think that a table join is necessary to attach the names somehow, or (if this is even possible) nesting another query within the array that's returned by the query which then queries for each name individually.
Any ideas??
Upvotes: 0
Views: 86
Reputation: 1416
If the friends
table contains unordered pairs of friends and each message is related to exactly one such pair (from + to), you could consider to change the design of your database so that each message get a reference to this pair (say, friends_id
).
This extra data can speed up your queries because SELECT
s with different join conditions combined with OR
are nearly inoptimizable.
Upvotes: 0
Reputation: 4456
You can get the two user ids using table name or aliases, like:
SELECT users.user_id, friends.user_id AS friend_user_id ...
Upvotes: 2