Facing trouble while fetching data to yajra datatables with model laravel

Question

I have the following three table in laravel app

purchases table

id, name, price

Products table

id, purchase_id, price

sales table

id, product_id, quantity, total-price

The sales table belongs to products table refers product id while products table belong to purchases table refers purchase id

Now i I want to fetch data in sales tables but also in need the name of the product which come from purchases table the final results must be

new table

id, product_id, quantity, total-price, name

my query below fetch only sales table data

$sales = Sales::whereBetween(DB::raw('DATE(created_at)'), array($from_date, $to_date))->get();

here I return yajira datatable

return Datatables::of($sales)->addIndexColumn()->make(true);

other code in models

sales model

public function product()
{
    return $this->belongsTo(Product::class, 'product_id');
}

public function purchase()
{
    return $this->belongsTo(Purchase::class,'purchase_id');
}

products model

public function purchase()
{
    return $this->belongsTo(Purchase::class);
}

purchases model

public function category()
{
    return $this->belongsTo(Category::class);
}

public function supplier()
{
    return $this->belongsTo(Supplier::class);
}

Answer 1

You need to do join operation for this.

Sales::query()
      ->leftJoin('products', 'sales.product_id', '=', 'products.id')
      ->leftJoin('purchases', 'products.purchase_id', '=', 'purchases.id')
      ->whereBetween(DB::raw('DATE(sales.created_at)'), [$from_date, $to_date])
      ->select('sales.id', 'sales.product_id', 'sales.quantity', 'sales.total-price', 'purchases.name');

You can learn more from the documentation