Append a query to model

I have a table named products that has 2 columns named (id, name) and listed the table in view using this query:

$rows = Product::paginate();

I want to append remaining quantity to rows query like this.

DB::select("SELECT IFNULL(q.bought, 0) - IFNULL(q.sold, 0) AS remain FROM products LEFT JOIN (SELECT buy_details.product_id, SUM(buy_details.quantity) AS bought, SUM(sell_details.quantity) AS sold FROM buy_details LEFT JOIN sell_details ON buy_details.product_id = sell_details.product_id GROUP BY buy_details.product_id) q ON products.id = q.product_id");

Thanks in advanced

Armani

5 years ago

Level 17

I tried to use leftJoinSub but it gives me this error:

 A subquery must be a query builder instance, a Closure, or a string.

This is the query:

$remain = DB::select("SELECT producs.id, IFNULL(q.bought, 0) - IFNULL(q.sold, 0) AS remain FROM products LEFT JOIN (SELECT buy_details.product_id, SUM(buy_details.quantity) AS bought, SUM(sell_details.quantity) AS sold FROM buy_details LEFT JOIN sell_details ON buy_details.product_id = sell_details.product_id GROUP BY buy_details.product_id) q ON products.id = q.product_id");

$rows = Product::leftJoinSub($remain, 'remain', function ($join) {
            $join->on('products.id', '=', 'remain.id');
        })->paginate();

Please or to participate in this conversation.