如何从Laravel Eloquent中的另一个表中检索行？

Question

I have 3 tables: posts, votes and users. I have written a neat code with Eloquent to retrieve just the posts where the user has not voted yet on.

$posts = Post::whereDoesntHave("votes")
                    ->where('user_id', '=', $user_id)
                    ->whereBetween('posts.created_at', array(Carbon::now()->subHours(48), Carbon::now()))
                    ->take(20)
                    ->get();

return response()->json(['posts' => $posts])->withCallback($request->input('callback')); 

But also I want to retrieve user name from table users. I want to pass the data with json.

If I try to do it with query builder, it is hard to eliminate posts that have been voted already by the user.

Answer 1

You can do a join manually to the user table

$posts = Post::join('users', 'users.id', '=', 'posts.user_id')
                ->whereDoesntHave("votes")
                ->where('user_id', '=', $user_id)
                ->whereBetween('posts.created_at', array(Carbon::now()->subHours(48), Carbon::now()))
                ->take(20)
                ->get();

Or you could define a relationship in the Post model class.

public function user()
{
   return $this->belongsTo('App\User');
}

Then you use with('user') to retrieve data from user table.

$posts = Post::with('user')
                ->whereDoesntHave("votes")
                ->where('user_id', '=', $user_id)
                ->whereBetween('posts.created_at', array(Carbon::now()->subHours(48), Carbon::now()))
                ->take(20)
                ->get();