retrieve rows based on parent relationship

This is the query:

$checklist_items = AppModelsChecklistItem::with('checklist')
                ->where('checklist.ambulance_id', '=', $amb_id)->where('item_stock_id', '=', $item->item_stock_id)->where('used', '=', 0)->get();

I am trying to get all the checklist items that have the same item_stock_id from the checklists that have the same ambulance_id.

However, the query from above is not working, showing me this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'checklist.ambulance_id' in 'where clause'
select
  *
from
  `checklist_items`
where
  `checklist`.`ambulance_id` = 1
  and `item_stock_id` = 1549
  and `used` = 0

Below is the ChecklistItem model:

class ChecklistItem extends Model
{
    use HasFactory;

    protected $guarded = [];
    
    public $timestamps = false;

    public function item() {
        return $this->hasMany( Item::class );
    }

    public function item_stock() {
        return $this->hasMany( ItemStock::class );
    }

    public function checklist() {
        return $this->hasMany( Checklist::class );
    }

    public function checklist_item() {
        return $this->belongsTo( ChecklistItem::class);
    }
}

And this is the Checklist model:

class Checklist extends Model
{
    use HasFactory;

    protected $fillable = [
        'inventory_id', 
        'medic_id', 
        'ambulance_id', 
        'checklist_date',
        'patient_number',
        'tour'
    ]; 

    protected $guarded = [];
    
    public $timestamps = false;

    public function medic() {
        return $this->belongsTo( Medic::class );
    }

    public function inventory() {
        return $this->belongsTo( Inventory::class );
    }

    public function ambulance() {
        return $this->belongsTo( Ambulance::class );
    }

    public function checklistitems() {
        return $this->hasMany( ChecklistItem::class);
    }

    public function assistent() {
        return $this->belongsTo( Assistent::class );
    }

    public function ambulancier() {
        return $this->belongsTo( Ambulancier::class );
    }
}

Answer

To constrain the eager load you need to specify the constraint within the subquery of with. You could try the below

$checklist_items = AppModelsChecklistItem::with([
       'checklist' => fn($query) => $query->where('ambulance_id', $amb_id)
    ])
    ->where('item_stock_id', '=', $item->item_stock_id)
    ->where('used', '=', 0)
    ->get();

Advertisement

Answer