laravel multiple relation query

Question

I have 3 model in my project and i want to get a query or collection result as i say:

1. JewelsItem model:

   protected $table = 'jewel_items';
   
   public function jewel(){
    return $this->belongsTo('App\Models\Jewel');
   }
   
    public function sellInvoice(){
    return $this->belongsTo(SellInvoice::class,'sell_invoice_id');
   }
   

2.Jewel model:

public function jewelsItems(){
    return $this->hasMany('App\Models\JewelsItem');
}

3.sellInvoice model:

    protected $table = "sell_invoices";

public function jewelsItems(){
    return $this->hasMany(JewelsItem::class,'buy_invoice_id');
}

Query: i want to get all jewelsitems that has no sellInvoice and has jewel name like 'something'.

note: jewel model has a name attribute .and i want to add name of jewel to first of all the collection's items of result.

i know how to get all jewel with name of 'something' :

Jewel::where('name','like','something'.'%')->get();

but i can't get all jewelItem related to it an add name of jewel to first of them.

Answer 1

To look for condition in another relationship you can use whereHas() and whereDoesntHave()

$name = 'something';
$items = JewelsItem::whereHas('jewel', function($query) use ($name) {
    $query->where('name', 'LIKE', "%{$name}%");
})
->whereDoesntHave('sellInvoice')
->with('jewel')
->get();

It reads: getting jewel items where jewel has $name in the name field
and doesn't have sell invoice
then add related jewel to the jewel item.

When you want to retrieve the name of the jewel, you can access its property like so.

foreach($items as $item) {
    echo $item->jewel->name;
}