How to properly merge tables so that in the first table the column with user_id is replaced by username from the second table?

Question

Laravel

How to properly merge tables so that in the first table the column with user_id is replaced by username from the second table?
I have a posts table which has a user_id(int) which I want to replace with a username from the users table which has an id(int). I wrote this query:

$posts = DB::table('posts')
      ->join('users', 'posts.user_id', '=', 'users.id')
      ->orderBy('publish_at', 'desc')->get()

What have I done wrong?

Answer 1

In Post model:

public function user()
{
  return $this->belongsTo(User::class);
}

Then:
Then each post will have a user value.
Or like this:

$posts = DB::table('posts')->select(DB::raw('posts.*, users.username'))
      ->join('users', 'posts.user_id', '=', 'users.id')
      ->orderBy('publish_at', 'desc')->get();