Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Isn't this the same question as you asked yesterday? <dba.stackexchange.com/questions/22399/…; –  Mr.Brownstone Aug 14 '12 at 18:52
    
@Mr.Brownstone Kind of I vote for the other to be deleted because I can't delete, this is better explain and better focused I think –  ReynierPM Aug 14 '12 at 18:54
    
Ok, why does this have to be in a single query? And can you change the design of the database at all? –  Mr.Brownstone Aug 14 '12 at 18:59
    
@Mr.Brownstone Needs to be a single query due to LIMIT I think and yes I can change the DB design. I'll uploaded a image here dropmocks.com/mBkERq with my design maybe and probably it's wrong. I'm trying to get something like Facebook Wall status and comment from users and friends of users and if it's posibble friends of my friends –  ReynierPM Aug 14 '12 at 19:03
    
ok - give me a moment to knock something up :) –  Mr.Brownstone Aug 14 '12 at 19:11
add comment

1 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?

share|improve this answer
    
@mr-brownstone not necessary I need to create the same in the other direction because the field approved says if the relation was accepted or not. So if I invited user with ID=2 then friend_id becomes 2 and user_id becomes 1 and approved becomes 0, when user_id = 2 accept the invitation then approved becomes 1 and this is the flag that tell me if exists or not a relationship between 1 and 2, I'm right or not? –  ReynierPM Aug 14 '12 at 19:40
    
Yes - but logically say ID 2 posted a status and ID 1 commented on it. Then the user would be 2 and the friend would be 1, but in the friend table it would be the other way around - making maintenance confusing. However, it's personal taste. Is the result that I produced near what you are looking for at all? if not can you provide an example so that I can work towards? Thanks. –  Mr.Brownstone Aug 14 '12 at 19:46
    
@mr-brownstone Yes, you're right is a bit confusing and I would think on that before needs to try to see what happens in that case where relation was created as 1,2 and then 2 publish a status and 1 comments over that status maybe doing some tricks at comment or status tables solve the problem not so sure right now. As for your result is near what I'm looking but why the records 1 and 2 have the same message? Is due to data I leave as sample? –  ReynierPM Aug 14 '12 at 19:59
    
It's just the data that you posted was the same for those two entries :) –  Mr.Brownstone Aug 14 '12 at 20:01
    
@mr-brownstone also I've try your suggestion before but query ground up and start to be complex because this is not the only query related to this, I need to check which are friends of mine which not which have unantended invitations and some others and because of that, basically, I end using this other solution –  ReynierPM Aug 14 '12 at 20:01
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.