Add result of subquery to Eloquent query in Laravel 9

Question

In Laravel 9 I am trying to add the result of a subquery to a query(for lack of better wording) and I am stuck. More concretely, I am trying to load all products and at the same time add information about whether the current user has bought that product.

Why do I want to do this? I am currently loading all products, then loading all bought products, then comparing the 2 to determine if the user has bought a product, but that means extra queries which I would like to avoid. Pretend for the sake of this question that pagination doesn't exist(because when paginating the impact of those multiple queries is far diminished).

There is a many to many relationship between the 2 tables users and products, so these relationships are defined on the models:

public function products()
{
    return $this->belongsToMany(Product::class);
}

and

public function users()
{
    return $this->belongsToMany(User::class);
}

What I have tried so far:

I created a model for the join table and tried to use selectRaw to add the extra 'column' I want. This throws a SQL syntax error and I couldn't fix it.

$products = Product::query()
        ->select('id', 'name')
        ->selectRaw("ProductUser::where('user_id',$user->id)->where('product_id','products.id')->exists() as is_bought_by_auth_user")
        ->get();

I tried to use addSelect but that also didn't work.

$products = Product::query()
    ->select('id', 'name')
    ->addSelect(['is_bought_by_auth_user' => ProductUser::select('product_id')->where('user_id',$user?->id)->where('product_id','product.id')->first()])
    ->get();

I don't even need a select, I actually just need ProductUser::where('user_id',$user?->id)->where('product_id','product.id')->exists() but I don't know a method like addSelect for that. The ProductUser table is defined fine btw, tried ProductUser::where('user_id',$user?->id)->where('product_id','product.id')->exists() with hardcoded product id and that worked as expected.

I tried to create a method on the product model hasBeenBoughtByAuthUser in which I wanted to check if Auth::user() bought the product but Auth wasn't recognized for some reason(and I thought it's not really nice to use Auth in the model anyway so didn't dig super deep with this approach).

    $products = Product::query()
                ->select('id', 'name')
                ->addSelect(\DB::raw("(EXISTS (SELECT * FROM product_user WHERE product_users.product_id = product.id AND product_users.user_id = " . $user->id . ")) as is_bought_by_auth_user"))
                ->simplePaginate(40);

For all attempts $user=$request->user().

I don't know if I am missing something easy here but any hints in the right direction would be appreciated(would prefer not to use https://laravel.com/docs/9.x/eloquent-resources but if there is no other option I will try that as well).

Thanks for reading!

userRjx5 · Answer

This should do,

$id = auth()->user()->id;

$products = Product::select(
    'id', 
    'name', 
    DB::raw(
        '(CASE WHEN EXISTS (
            SELECT 1 
            FROM product_users
            WHERE product_users.product_id = products.id 
            AND product_users.user_id = '.$id.'
        ) THEN "yes" ELSE "no" END) AS purchased'
    )
);

return $products->paginate(10);

the collection will have purchased data which either have yes or no value

Add result of subquery to Eloquent query in Laravel 9

1 Answers