Complex JOIN SQL query in Laravel 5

I have three tables that I need to join: products, orders_items, orders

The products table contains, obviously, the products. When a user adds a product to their cart, it creates an order and a line item in the order_items table. When the user pays, it puts the total paid into the orders table. Therefore, purchased products removed from inventory only include products that are attached to an order_item attached to an order that has been marked paid.

I need to return all the products, but also somehow calculate how many are left in inventory. The products table contains a field called "initial_quantity" which is how many of the product we started with.

Here is my current query, which works just fine for returning all the products of a certain type:

$swims = Product::where('product_type_id', 1)
            ->whereDate('visible_date', '<=', Carbon::now())
            ->where('active', true)
            ->where(function ($query) {
                $query->where('end_date', '>=', Carbon::now())
                      ->orWhere('end_date', null);  
            })          
            ->get();

The above query returns four results. Here's my current attempt:

$swims = Product::where('product_type_id', 1)
            ->whereDate('visible_date', '<=', Carbon::now())
            ->where('active', true)
            ->where(function ($query) {
                $query->where('end_date', '>=', Carbon::now())
                      ->orWhere('end_date', null);  
            })
            ->join('order_items', 'products.id', '=', 'order_items.product_id')
            ->leftJoin('orders', 'order_items.order_id', '=', 'orders.id')
            ->whereNotNull('orders.paid')          
            ->get();

This query returns only two results, which is not what I want.

I would like a query that returns all products I'm searching for and also how many are remaining. I've tried join, leftJoin, rightJoin, subquery joins and unions. Laravel doesn't seem to have outer joins or I would have tried those too.

Can anybody come up with the query I'm looking for? If not in Laravel then in raw SQL would be fine and I can use it as-is or try to Laravelize it.

Thank you in advance.

via Chebli Mohamed