Question 183586 in Laravel Question posted in
The official Laravel Documentation can be found here.

Facing trouble while fetching data to yajra datatables with model laravel

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);
}
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You just need to define your relationships properly. The Sales model should be related to the Purchases table with a has-one-through relationship. Then it’s a simple matter of eager loading the relationship.

    class Sales extends Model {
    public function purchase() {
        return $this->hasOneThrough(Purchase::class, Product::class);
    }
}

    And there’s no need to use a raw query in the controller as long as your 2 date variables are correct:

    public function showTable() {
    // assuming these are coming from the request?
    $from_date = (new Carbon(request()->get('start'))->startOfDay();
    $to_date (new Carbon(request()->get('end'))->endOfDay();
    $sales = Sales::with('purchase')
        ->whereBetween('created_at', [$from_date, $to_date])
        ->get();
    return Datatables::of($sales)->addIndexColumn()->make(true);
}

    On the front end, the data will be available through purchase.name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search