How do I write the following SQL query in Laravel using Eloquent?

Question

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.

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.

Does this query correct ? What is for WHERE user_id = user_id ? — Sagar Gautam
– Sagar Gautam, Commented Jul 31, 2017 at 6:18

deltaforce · Accepted Answer · 2017-07-31 09:21:45Z

3

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 !!!!

edited Jul 31, 2017 at 9:21

deltaforce

5341 gold badge9 silver badges26 bronze badges

answered Jul 31, 2017 at 6:27

AddWeb Solution Pvt Ltd

21.8k5 gold badges30 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

deltaforce Over a year ago

Actually i tried this first before asking the question. The problem here is that I get an error saying that user_id is not defined, although it was defined within the scope.

Martin Over a year ago

@SaviourTom because you have to use this variable in function like use ($user_id) after function($query) so it will look like: function($query) use ($user_id). By the way answer which you've voted as the best answer is by using Query Builder and not the Eloquent.

deltaforce Over a year ago

I was thinking why it was'nt working. Thanks a lot for the answer. @forexknight

AddWeb Solution Pvt Ltd Over a year ago

Oh! thank @forexknight to draw my attention on use()

Sagar Gautam · Accepted Answer · 2017-07-31 06:19:25Z

2

Try this one,

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

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

answered Jul 31, 2017 at 6:19

Sagar Gautam

9,4099 gold badges62 silver badges95 bronze badges

2 Comments

deltaforce Over a year ago

Thanks a lot for the answer. It works. But i wanted to use eloquent so as to optimize performance.

Sagar Gautam Over a year ago

I've thought you are not using eloquent so I've added my answer

Collectives™ on Stack Overflow

How do I write the following SQL query in Laravel using Eloquent?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related