I Have two tables,** table1 = products**

products table contains same products which are purchased on different date and have different price but same name.

Table2 : InvoiceItems table

table 2 contains invoices for above products

Relationship

class Products extends Model
{
 
    public function soldproducts(): HasMany
    {
        return $this->hasMany(InvoiceItem::class, 'product_id','id');
    }

}

class InvoiceItem extends Model
{
   public function products()
    {
        return $this->belongsToMany('App\Models\Products', 'product_id','id');
    }
}

I grouped both tables by their name

$table1=   DB::table('products')
        ->groupBy('products.name')
        ->select(DB::raw('products.name, sum(products.qty) as totalqty'))
        ->get();
      

$table2 = DB::table('invoice_items')
        ->groupBy('invoice_items.name')
        ->select(DB::raw('invoice_items.name, sum(invoice_items.product_qty) as soldqty')) 
        ->get();

Both above query giving desired result but

I need difference Balance QTy = totalqty - soldqty

How can I substract these two tables?

CodePudding user response：

Try using a leftJoin:

DB::table('products')
    ->leftJoin('invoice_items', 'products.id', '=', 'invoice_items.product_id')
    ->groupBy('products.name')
    ->select(DB::raw('products.name, sum(products.qty) - sum(invoice_items.product_qty) as diffqty'))
    ->get();

CodePudding user response：

To get the difference between the quantities from the two tables, use a left join to join the two tables together and then use a select statement to subtract the soldqty column from the totalqty column.

$results = DB::table('products')
    ->leftJoin('invoice_items', 'products.name', '=', 'invoice_items.name')
    ->groupBy('products.name')
    ->select(DB::raw('products.name, sum(products.qty) - sum(invoice_items.product_qty) as balance_qty'))
    ->get();