I'm building an application that allows users to recommend music to each other, and am having trouble building a query that would return a 'stream' of recommendations that involve both the user themselves, as well as any of the user's friends. This is my table structure:
Recommendations
ID Sender Recipient [other columns...]
-- ------ --------- ------------------
r1 u1 u3 ...
r2 u3 u2 ...
r3 u4 u3 ...
Users
ID Email First Name Last Name [other columns...]
--- ----- ---------- --------- ------------------
u1 ... ... ... ...
u2 ... ... ... ...
u3 ... ... ... ...
u4 ... ... ... ...
Relationships
ID Sender Recipient Status [other columns...]
--- ------ --------- -------- ------------------
rl1 u1 u2 accepted ...
rl2 u3 u1 accepted ...
rl3 u1 u4 accepted ...
rl4 u3 u2 accepted ...
So for user 'u4' (who is friends with 'u1'), I want to query for a 'stream' of recommendations relevant to u4. This stream would include all recommendations in which either the sender or recipient is u4, as well as all recommendations in which the sender or recipient is u1 (the friend).
This is what I have for the query so far:
SELECT * FROM recommendations
WHERE recommendations.sender IN
( SELECT sender FROM relationships WHERE recipient='u4' AND status='accepted'
UNION
SELECT recipient FROM relationships WHERE sender='u4' AND status='accepted')
OR recommendations.recipient IN
( SELECT sender FROM relationships WHERE recipient='u4' AND status='accepted'
UNION
SELECT recipient FROM relationships WHERE sender='u4' AND status='accepted')
UNION
SELECT * FROM recommendations
WHERE recommendations.sender='u4' OR recommendations.recipient='u4'
GROUP BY recommendations.id
ORDER BY datecreated DESC
Which seems to work, as far as I can see (I'm no SQL expert). It returns all of the records from the Recommendations table that would be 'relevant' to a given user. However, I'm now having trouble also getting data from the Users table as well. The Recommendations table has the sender's and recipient's ID (foreign keys), but I'd also like to get the first and last name of each as well. I think I require some sort of JOIN, but I'm lost on how to proceed, and was looking for help on that. (And also, if anyone sees any areas for improvement in my current query, I'm all ears.)
Thanks!
