Laravel filter one to many relationship

user_8740944 0
Viewed 391

I'm trying to filter a query on a model that has a one to many relationship, but it's not working. Correct output should remove all stocks that are expired, but it returns all the available stocks.

Product: hasMany(Stock::class)
Stock: belongsTo(Product::class)

code:

$keyword = $request->keyword;
$data = Product::with('stocks')
            ->where([
                ['pharmacy_id', '=', auth()->user()->pharmacy->id],
            ])
            ->where(function($productModel) use($keyword) {
                $productModel->where('name', 'LIKE', "%$keyword%")->orWhere('sku', 'LIKE', "%$keyword%");
            })
            ->whereHas('stocks', function($stockModel) {
                $stockModel->where('expiry', '>', Carbon::now()->format('Y-m-d'));
            })->get();

Current output:

[
  {
    "id": 1,
    "sku": "xpzidfa1454995",
    "name": "Jaqueline Will",
    "category": "medicine",
    "type": "manufactured",
    "selling_price": "1.00",
    "pharmacy_id": 1,
    "created_at": "2020-12-22T11:40:32.000000Z",
    "updated_at": "2020-12-22T11:40:32.000000Z",
    "stocks": [
      {
        "id": 3,
        "product_id": 1,
        "expiry": "2025-10-10",
        "amount": 40,
        "supplier_id": 1,
        "pharmacy_id": 1,
        "created_at": "2020-12-22T11:45:39.000000Z",
        "updated_at": "2020-12-22T11:45:39.000000Z"
      },
      {
        "id": 5,
        "product_id": 1,
        "expiry": "2020-10-10",
        "amount": 40,
        "supplier_id": 1,
        "pharmacy_id": 1,
        "created_at": "2020-12-22T11:45:39.000000Z",
        "updated_at": "2020-12-22T11:45:39.000000Z"
      }
    ]
  }
]

Correct output:

[
  {
    "id": 1,
    "sku": "xpzidfa1454995",
    "name": "Jaqueline Will",
    "category": "medicine",
    "type": "manufactured",
    "selling_price": "1.00",
    "pharmacy_id": 1,
    "created_at": "2020-12-22T11:40:32.000000Z",
    "updated_at": "2020-12-22T11:40:32.000000Z",
    "stocks": [
      {
        "id": 3,
        "product_id": 1,
        "expiry": "2025-10-10",
        "amount": 40,
        "supplier_id": 1,
        "pharmacy_id": 1,
        "created_at": "2020-12-22T11:45:39.000000Z",
        "updated_at": "2020-12-22T11:45:39.000000Z"
      }
    ]
  }
]

Stock Model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Stock extends Model
{
    use HasFactory;

    protected $fillable = [
        'product_id',
        'expiry',
        'amount',
        'supplier_id',
        'pharmacy_id',
    ];

    public function pharmacy() {
        return $this->belongsTo(Pharmacy::class);
    }

    public function product() {
        return $this->belongsTo(Product::class);
    }
}
laravel mysql php database
2 Answers

To remove the stock records which are expired, the constraint should be placed on the eager load

$keyword = $request->keyword;
$data = Product::with(['stocks' => fn($query) => $query->whereDate('expiry', '>', now())])
    ->where([
        ['pharmacy_id', '=', auth()->user()->pharmacy->id],
    ])
    ->where(function($productModel) use($keyword) {
        $productModel->where('name', 'LIKE', "%$keyword%")->orWhere('sku', 'LIKE', "%$keyword%");
    })
    ->get();
user_12203640

I think you should match date using whereDate.

Try this,

$keyword = $request->keyword;
$data = Product::with('stocks')
            ->where([
                ['pharmacy_id', '=', auth()->user()->pharmacy->id],
            ])
            ->where(function($productModel) use($keyword) {
                $productModel->where('name', 'LIKE', "%$keyword%")->orWhere('sku', 'LIKE', "%$keyword%");
            })
            ->whereHas('stocks', function($stockModel) {
                $stockModel->whereDate('expiry', '>', Carbon::now()->format('Y-m-d'));
            })->get();

Check this link for more

Laravel Additional Where Clauses

user_110670200
Related