CODEWITHSUNDEEP
laraveleloquent
Ahmad Karimi
Ahmad Karimi

Reputation: 1383

How to query a table based on criteria of another table in Laravel?

In my laravel project I have an inventory and a medicine table which the formats are as the following:

Inventory Table

  id   |   medicine_id   |  expiry_date
-------|-----------------|-------------
  1    |       1         |  13-11-2021
  2    |       2         |  01-01-2020
  3    |       2         |  23-05-2024

Medicine Table

  id   |     name        |  category
-------|-----------------|-------------
  1    |    Hemophine    |  Syringe
  2    |   andrenalin    |  Tablet
  3    |    aspirin      |  Capsule

The models are set as below:

Inventory Model

class Inventory extends Model
{
    public $incrementing = false;

    public function medicine(){
        return $this->belongsTo('App\Medicine', 'medicine_id');
    }
}

Medicine Model

class Medicine extends Model
{
    public $incrementing = false;

    public function inventory(){
       return $this->hasMany('App\Inventory', 'medicine_id');
    }
}

For retrieving all the inventories with a specific category such as Syringe, I tried eager loading, but couldn't figure out the flaws. Here is what I did to get all the inventories with a Syringe category, but it didn't work.

public function index()
    {

        $medicines = Inventory::where('medicine_id'->category, "Syringe")->get();

        foreach($medicines as $medicine){
            echo $medicine->medicine->name ." ".$medicine->expiry_date;
            echo "<br>";
        }

    }

Now, Is there anyway to get all the inventory items based on their categories they are in? in this case in "Syringe" category?

Upvotes: 0

Views: 348

Answers (1)

nakov
nakov

Reputation: 14298

Your syntax is a bit wrong, in order to load the medicine for each inventory you need to use with and the where function is where('column', value) So change it to this:

$medicines = Inventory::with([
  'medicine' => function($query) {
       $query->where('category', "Syringe");
  }    
])->get();

or even better the other way around:

$medicines = Medicine::with('inventory')->where('category', 'Syringe')->get();

Upvotes: 3

Related Questions