Query where the column is in another table

Question

I have two tables, posts and likes . I need to create a request using Eloquent, which receives all the messages that a particular user_id liked.

In other words, it should be something like this:

 SELECT * FROM posts p LEFT JOIN likes l ON p.id = l.post_id WHERE l.user_id = 2 ORDER BY l.created_at DESC 

posts table:

 +----+---------+------------+-------------+ | id | user_id | message | created_at | +----+---------+------------+-------------+ | 1 | 2 | Hello! | <SOME TIME> | | 2 | 3 | World! | <SOME TIME> | | 3 | 2 | Something. | <SOME TIME> | | 4 | 2 | Another. | <SOME TIME> | +----+---------+------------+-------------+ 

likes table:

 +----+---------+---------+-------------+ | id | post_id | user_id | created_at | +----+---------+---------+-------------+ | 1 | 1 | 2 | <SOME TIME> | | 2 | 2 | 2 | <SOME TIME> | | 3 | 1 | 3 | <SOME TIME> | | 4 | 3 | 2 | <SOME TIME> | +----+---------+---------+-------------+ 

Here is my Post class:

 <?php class Post extends Eloquent { protected $table = 'posts'; public function likes() { return $this->hasMany('Like'); } } 

And the Like class:

 <?php class Like extends Eloquent { protected $table = 'likes'; public function post() { return $this->belongsTo('Post'); } } 

How can i do this?

Answer 1

this should work:

 $user_id = //however you get the userid here. $posts = Post::whereHas('likes', function ($q) use($user_id){ $q->where('user_id', $user_id); })->get(); 

Answer 2

You can use the Laravel DB class to perform joins in two or more tables, as follows, how your query will be executed in laravel:

 $users = DB::table('posts') ->leftJoin('likes', 'posts.id', '=', 'likes.post_id') ->select('posts.*', 'likes.*') ->where('likes.user_id', '=', '2') ->orderBy('likes.created_at', 'desc') ->get(); 

Remember to use the DB class at the top of your controller;

If you want to do this with eloquence, you must do the following:

 $result = Post::whereHas('likes', function ($q) use($user_id) { $q->where('user_id', $user_id); }) ->orderBy('likes.created_at') ->get();