CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Calum Murray
Calum Murray

Reputation: 1192

SQL Query to retrieve messages from all user's friends

I have 3 databases setup for a Twitter-clone website.

Table 'users':

email | firstname | lastname | hash_password

Link Table 'friends':

email | friend

This lists all users and their friends. Friendships are 1-way (like Twitter's "Following"/"Followers"). friends table is a link table to list all users' friends. So both friends.user and friends.friend are foreign keys to users.email.

Table 'messages':

timestamp | user | content

What is an SQL query to retrieve all messages from a user and his friends?

I've tried:

SELECT
  'timestamp', 
  user, 
  content 
FROM
  messages
  INNER JOIN friends ON messages.user = friends.friend
ORDER BY 'timestamp' DESC;

This seems to join them correctly, but how can I only get the messages for a specific user's (email address) friends. Right now this just returns all messages.

Thanks.

Upvotes: 1

Views: 1754

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270775

The quoting of timestamp should be with backticks, otherwise MySQL will assume a string literal value.

Otherwise, to return messages from both the user and all friends, you may use a UNION. One half returns the user's messages, the other half returns friends' messages. You need to add the user's email to your join condition:

/* First part of the UNION returns friends messages */
SELECT
  `timestamp`, 
  user, 
  content 
FROM
  messages 
  /* The join ON clause specifies the main user email */
  INNER JOIN friends 
    ON messages.user = friends.friend
       AND friends.email = 'email@example.com'
UNION ALL
/* Other part of the UNION just gets all messages for the main user by email addr */
SELECT
  `timestamp`,
  user,
  content
FROM
  messages
WHERE user = 'email@example.com'
/* ORDER BY applies to everything */
ORDER BY `timestamp` DESC;

And if you want to join in the users info (firstname/lastname) here, the easiest way to go about it would be to wrap the entire thing in a subquery and join.

SELECT
  users.*,
  messages_sub.content,
  messages_sub.`timestamp`
FROM
  users
  JOIN (/* The entire thing from above */) AS messages_sub ON users.email = messages_sub.user
ORDER BY `timestamp`

It can also be done with a UNION of the literal email address you want to find and the friend list, producing only one outer query. This is a little trickier, but may be ultimately faster. It will also be less confusing to bring in other columns from the users table here. I'll add the names:

SELECT
  `timestamp`,
  user,
  firstname,
  lastname,
  content
FROM
  messages
  INNER JOIN (
    /* Union query produces a list of email addrs - 
       the person you're looking for plus all his friends
       Starts with string literal for the main user
    */
    SELECT 'email@example.com' AS email
    UNION 
    /* Plus all his friends into one list joined against messages */
    SELECT friend AS email FROM friends WHERE email = 'email@example.com'
  ) user_and_friends ON messages.user = user_and_friends.email
  /* Join against the users table for name info */
  INNER JOIN users ON user_and_friends.email = users.email
ORDER BY `timestamp` DESC

Upvotes: 2

Related Questions