Select from select using Laravel eloquent

Question

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 1

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` = ?)

Answer 2

What do you mean to saying "How can I do it"? Are you asking how to structure it or how to query with eloquent?

If you asking the query:

Product::whereIn('id', Favourite::select('id')->whereUserId(Auth::id())->get()->toArray())->get();

should work.

---

If you asking structure, you should define relations in models.

On User model;

public function favourites()
{
    return $this->hasMany(Favourite::class, 'user_id');
}

and on Favourite model;

public function favoriteProduct()
{
    return $this->belongsTo(Product::class, '[the product_id column on favourites table]');
}

and then you can access like;

$userFavourites = User::with('favourites.favoriteProduct')->whereId(Auth::id())->favourites;

on your view:

@foreach($userFavourites as $userFavourite)
    {{ $userFavourite->favouriteProduct }}
@endforeach

this is the simplest. on the other hand; you can use Has Many Through relationship.

And this would be the cleanest way.