Laravel复杂查询

I am new to Laravel and building a small Laravel 5.3 app offering free content files as well as files for purchase.

I want users to automatically have access to the free files (content which may be added periodically).

I have a products, purchases (pivot) and users table.

When a user is logged in, how can I query the products table like the following: select all free products (price=0) or join on purchases where users.user_id = purchases.user_id and products.id = purchases.product_id?

Any ideas, or is there a better way to accomplish the same thing?

Thanks

How about if you use following query:

$purchasedProducts = DB::table('purchases')
            ->join('products', 'products.id', '=', 'purchases.product_id')
            ->select('purchases.*', 'products.*')
            ->where([
               ['purchases.user_id', '=', $loggedinUserId],
               ['products.price', '=', 0],
               ])
            ->get();

If you have defined the relations between user and product then you can query it using orWhereHas as:

Product::where('price', 0)
        ->orWhereHas('users', function ($q) user($user) {
            $q->where('user_id', $user->id);
        })
        ->get();

Assuming your relation name is users.