Several values from different tables

Question

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest default_friend. Here is the SQL for those tables: http://pastebin.com/PxGZ8QYz with some values for testing purpose. So how this should work: a user can have 0:n friend so I insert new records in default_friend table depending on which part start the relation. Suppose I'm user with ID=1 (admin) and want to invite user with ID=2 (demo) then in the default_friend table I insert a record where friend_id = 2 and user_id = 1. Now each time I create a new user I insert data in default_users and default_profiles tables. Also each user can have 0:n status and also each status can have 0:m comment. Then I need to get, if is possible in one SQL query just, all the status and comment from me (ID=1) and also the same for each friend of mine(ID=2 by now could be more than that). I create this query:

SELECT DISTINCT 
  u.id, 
  p.display_name, 
  p.first_name, 
  p.last_name, 
  s.status_id, 
  s.message, 
  s.created_at AS sdate, 
  (SELECT friend_id FROM default_friend f WHERE f.friend_id = 1 ) AS f_friend_id, 
  (SELECT user_id FROM default_friend f WHERE f.user_id = 1 ) AS f_user_id   
FROM default_users u 
LEFT JOIN default_profiles p 
 ON ( 
  u.id = p.user_id OR f_friend_id = p.user_id
 ) 
LEFT JOIN default_status s 
 ON ( 
  u.id = s.user_id OR f_friend_id = s.user_id
 ) 
WHERE u.id = 1 
LIMIT 0, 8 

But this wont work because user_id = 1 could have 0:m friends.

Also tried this too:

SELECT DISTINCT 
  u.id, 
  p.display_name, 
  p.first_name, 
  p.last_name, 
  s.status_id, 
  s.message, 
  s.created_at AS sdate, 
  f.friend_id, 
  f.user_id 
FROM default_users u 
LEFT JOIN default_profiles p 
  ON( 
    u.id = p.user_id
  ) 
LEFT JOIN default_status s 
  ON( 
    u.id = s.user_id
  ) 
RIGHT JOIN default_friend f 
   ON ( 
     (f.friend_id = 1 OR f.user_id = 1) AND f.approved = 1
   ) 
WHERE u.id = 1 
LIMIT 0, 8

But this only give status from me and not from my friends as I want. This is the result of the second query:

[0] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 1
        [message] => dasdasdasdasdasd
        [sdate] => 2012-08-13 15:15:37
        [friend_id] => 2
        [user_id] => 1
    )

[1] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 3
        [message] => dasdsad344hbvnbnhjhgjhjghjhj
        [sdate] => 2012-08-13 17:24:53
        [friend_id] => 2
        [user_id] => 1
    )

EDIT: Here is a better approach/result but I get duplicates rows:

 SELECT DISTINCT 
     u.id, 
     p.display_name, 
     p.first_name, 
     p.last_name, 
     s.status_id, 
     s.message, 
     s.created_at AS sdate,  
     f.friend_id, 
     f.user_id 
 FROM default_users u 
 LEFT JOIN default_friend f 
    ON ( 
         (f.friend_id = 1 OR f.user_id = 1) 
           AND 
          f.approved = 1
       ) 
 LEFT JOIN default_status s 
    ON( 
      u.id = s.user_id 
        OR 
      f.friend_id = s.user_id
    ) 
 LEFT JOIN default_profiles p 
    ON( 
      s.user_id = p.user_id) 
 WHERE u.id = 1 
 LIMIT 0, 8

And here are the results for this one:

[0] => stdClass Object ( [id] => 1 [display_name] => Reynier Perez Mira [first_name] => Reynier [last_name] => Perez Mira [status_id] => 1 [message] => dasdasdasdasdasd [sdate] => 2012-08-13 15:15:37 [friend_id] => 2 [user_id] => 1 )

[1] => stdClass Object
    (
        [id] => 1
        [display_name] => Demo
        [first_name] => Demo
        [last_name] => Demo
        [status_id] => 2
        [message] => dasdasdasdasdasd
        [sdate] => 2012-08-13 15:16:03
        [friend_id] => 2
        [user_id] => 1
    )

[2] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 3
        [message] => dasdsad344hbvnbnhjhgjhjghjhj
        [sdate] => 2012-08-13 17:24:53
        [friend_id] => 2
        [user_id] => 1
    )

[3] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 1
        [message] => dasdasdasdasdasd
        [sdate] => 2012-08-13 15:15:37
        [friend_id] => 1
        [user_id] => 3
    )

[4] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 3
        [message] => dasdsad344hbvnbnhjhgjhjghjhj
        [sdate] => 2012-08-13 17:24:53
        [friend_id] => 1
        [user_id] => 3
    )

Nota: I added one more field to users table and friend and status to test this latest

EDIT 2: Ok, finally, tough, I found the solution, here it's:

SELECT DISTINCT 
   u.id, 
   p.display_name, 
   p.first_name, 
   p.last_name, 
   s.status_id, 
   s.message, 
   s.created_at AS sdate 
FROM default_users u 
LEFT JOIN default_friend f 
  ON(
      (f.friend_id = 1 OR f.user_id = 1) 
     AND 
      f.approved = 1 
    ) 
LEFT JOIN default_status s 
  ON( 
     u.id = s.user_id OR f.friend_id = s.user_id OR f.user_id = s.user_id 
    ) 
LEFT JOIN default_profiles p 
  ON( 
     s.user_id = p.user_id
    )
WHERE u.id = 1 
LIMIT 0, 8 

Which seems to be correct, by now, because didn't get duplicates and gets exactly what I'm looking for as can see below:

[0] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 1
        [message] => dasdasdasdasdasd
        [sdate] => 2012-08-13 15:15:37
    )

[1] => stdClass Object
    (
        [id] => 1
        [display_name] => Demo
        [first_name] => Demo
        [last_name] => Demo
        [status_id] => 2
        [message] => dasdasdasdasdasd
        [sdate] => 2012-08-13 15:16:03
    )

[2] => stdClass Object
    (
        [id] => 1
        [display_name] => Reynier Perez Mira
        [first_name] => Reynier
        [last_name] => Perez Mira
        [status_id] => 3
        [message] => dasdsad344hbvnbnhjhgjhjghjhj
        [sdate] => 2012-08-13 17:24:53
    )

[3] => stdClass Object
    (
        [id] => 1
        [display_name] => User 1
        [first_name] => User
        [last_name] => User
        [status_id] => 4
        [message] => dasdsad344hbvnbnhjhgjhjghjhjsdfsdfsdfsdfdsfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdf
        [sdate] => 2012-08-13 17:24:53
    )

But now I've another problem getting COUNT(*) as num_rows for that query to works because it returns 6 when should be 4 or I'm wrong?

PS: I must say that all tables will have 10 000 or more records so performance is required.

Cheers and thanks in advance

Answer 1

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?