我如何在Laravel中使用有说服力的语言编写以下SQL查询?

I want to write a query in Laravel to retrive all the posts that a particular user has viewed. The user_id and the post_id is saved in the table named WatchHistory.

我想在Laravel中编写一个查询，以检索特定用户查看的所有帖子。user_id和post_id保存在名为WatchHistory的表中。

SELECT *
FROM Post
WHERE post_id = (
     SELECT post_id
     FROM WatchHistory
     WHERE user_id = $user_id
);

I tried the following :

我试过以下方法:

$posts = Post::whereIn('post_id', function($query){
        $query->select('post_id')
            ->from(with(new WatchHistory)->getTable())
            ->where('user_id',$user_id);
        })->get();

But it gives me an error that the variable user_id is not defined.

但是它给我一个错误，变量user_id没有定义。

2 个解决方案

#1

You should try this :

你应该试试这个:

POST::whereIn('post_id', function($query) use($user_id){
            $query->select('post_id')
                ->from(with(new WATHCHISTORY)->getTable())
                ->where('user_id',$user_id);
            })->get();

Hope this work for you !!!!

希望这对你有用!!!!

#2

Try this one,

试试这个,

If your user's id is in variable $user_id, you can do it like,

如果用户的id在变量$user_id中，可以这样做，

DB::table('post')->whereIn('post_id',
    DB::table('watchhistory')->where('user_id',$user_id)->pluck('post_id')
)->get();

#1