Add result of subquery to Eloquent query in Laravel 9-CodePudding

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!

CodePudding user response：

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

EDIT If you want eloquent way you can try using withExists or withCount

i.e.

withExists the purchased field will have boolean value

$products = Product::select('id', 'name')->withExists(['users as purchased' => function($query) {
    $query->where('user_id', auth()->user()->id);
}]);

withCount the purchased field will have count of found relationship rows

$products = Product::select('id', 'name')->withCount(['users as purchased' => function($query) {
    $query->where('user_id', auth()->user()->id);
}]);