如何在laravel中创建获取帖子及其用户详细信息的查询

Question

I'm trying to fetch users Post and display their username & id below the post how can i create such query

Here is my Post Table

  ---------------------------------------------------------------------
  | id | user_id | title   | content   | status | time                |
  | 1  |   2     | example | Something | active | 2017-07-11 13:48:26 |
  | 2  |   2     | example | Something | active | 2017-07-11 13:48:26 |
  | 3  |   3     | example | Something | active | 2017-07-11 13:48:26 |
  | 4  |   4     | example | Something | active | 2017-07-11 13:48:26 |
  | 5  |   5     | example | Something | active | 2017-07-11 13:48:26 |
  ---------------------------------------------------------------------

User table

  ------------------------------------------------------------
  | id |  name   |password |    img    | last_login          |
  | 1  |  User1  | example | Something | 2017-07-11 13:48:26 |
  | 2  |  User2  | example | Something | 2017-07-11 13:48:26 |
  | 3  |  User3  | example | Something | 2017-07-11 13:48:26 |
  | 4  |  User4  | example | Something | 2017-07-11 13:48:26 |
  | 5  |  User5  | example | Something | 2017-07-11 13:48:26 |
  ------------------------------------------------------------

Now I would like to fetch all post from database and fetch from users table - name,img,username etc...

Here is what my query looks like till now

$p1 = App\Models\Post::where('status', 'active')
            ->orderBy('id', 'desc')
            ->take(6)
            ->get();

Answer 1

Try this... i used only JOIN because every post has a user.... you can refer this link https://laravel.com/docs/5.4/queries#joins

DB::table('post')
    ->select('post.*','user.id','user.name')
    ->join('user','user.id','=','post.user_id')
    ->get();

Try this for category just what you need to select just add it in select clause...

DB::table('post')
    ->select('post.*','user.id','user.name', 'category.*')
    ->join('user','user.id','=','post.user_id')
    ->join('category','post.id','=','category.post_id')
    ->get();