Get results from my own and "friends" posts

Question

Hi there!

I have a problem that I can't figure out myself. I've tried using LEFT JOIN etc but nothing seems to work. I'm using MySQL so you know.

I'm building a little blogportal for me and my friends and all users have their own blog.

database:

users: id, username, password, etc

blog: id, title, text, user_id, etc

relations follower_id, following_id

I query my own blogposts like this:

SELECT * FROM microblog WHERE user_id = {$user_id} ORDER BY posted DESC

and i list my friends like this:

SELECT * FROM users, relations WHERE relations.follower_id = {$user_id} AND relations.following_id = users.id

That was the easy part. BUT.

I rather JOIN the tables somehow because I also want to list my friends blogposts inside my loop. But I don't just want the post to show, I also want some info about the user that posted that one so then I must get some info from the users table as well. And that's what bothers me! I can't figure it out.

In short: I want to list my own blog posts and all the users I'm friend with within my own loop. And I also want to display username and email beside the posts.

Hope you understand what I mean.

/Tobias

Sweden

Answer 1

Try this?

SELECT m.* 
FROM microblog m
INNER JOIN users u ON m.user_id = u.user_id
LEFT JOIN relations r ON r.following_id = m.user_id
WHERE m.user_id = {$user_id}
OR (r.follower_id = {$user_id} AND NOT IsNull(r.follower_id))
ORDER BY posted DESC

Answer 2

How about?

select 
    u.username, 
    u.email, 
    m.title, 
    m.text 
       -- ... etc
from microblog m
     inner join user u on m.user_id = u.id
where  m.user_id = {$user_id} 
    or m.user_id in (select 
                         following_id 
                     from relations r 
                     where follower_id = {$user_id}
                    );

Answer 3

From my perspective I would pull the user data separately, store them into an array and access them when needed. This should be better for performance and would definitely be simpler.

select * from blog b, relations r 
where b.user = $user_id or ( b.user = r.follower_id and r.following_id = $user_id )
order by posted desc;

Not sure if I reversed follower/following.