Select from select using Laravel eloquent

I want to get all user’s favourite products. The query should be like this:

SELECT * 
FROM products 
WHERE id IN (SELECT id FROM favourites WHERE user_id == Auth::id())

How can I query with eloquent?

Answer

You’re trying to achieve something like this:

In the oficial documentation you can find the explanation of Advanced where clauses

DB::table('products')
    ->whereIn('id', function($query)
        {
            $query->select('id')
                  ->from('favourites')
                  ->where('favourites.user_id', Auth::id());
        })
    ->get()

The final result will be (you can see this dumping the query using toSql() method instead of get()) :

select * from `products` where `id` in (select `id` from `favourites` where `favourites`.`user_id` = ?)