从我自己和“朋友"的网站获取结果帖子 [英] Get results from my own and "friends" posts

问题描述

我有一个自己无法解决的问题.我试过使用LEFT JOIN等，但似乎没有任何效果.我正在使用MySQL，所以您知道.

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.

数据库:

用户: ID， 用户名， 密码， 等等

users: id, username, password, etc

博客: ID， 标题， 文本， 用户身份， 等等

blog: id, title, text, user_id, etc

关系 follower_id， following_id

relations follower_id, following_id

我这样查询自己的博客文章:

I query my own blogposts like this:

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

我这样列出我的朋友:

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

那是容易的部分.但是.

That was the easy part. BUT.

我宁愿以某种方式加入表，因为我还想在循环中列出我的朋友博客文章.但是我不仅希望显示该帖子，我还想要一些有关发布该帖子的用户的信息，因此我也必须从users表中获取一些信息.这就是困扰我的地方！我不知道.

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.

希望你明白我的意思.

/Tobias

瑞典

推荐答案

怎么样?

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}
                    );

这篇关于从我自己和“朋友"的网站获取结果帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！