#Eloquent query for searching a related table

3 messages · Page 1 of 1 (latest)

static pulsar
#

I have three tables:

Npcs
id, name, combat_level

Items
id, name

NpcDrops (uses foreign keys)
id, npc_id, item_id

I created models for each of these:

Npc

class Npc extends Model
{
    public function drops()
    {
        return $this->hasMany('App\Models\NpcDrop')->orderBy('drop_rate', 'asc');
    }
}

NpcDrop

class NpcDrop extends Model
{
    public function npc()
    {
        return $this->belongsTo('App\Models\Npc');
    }

    public function item()
    {
        return $this->belongsTo('App\Models\Item');
    }  
}

I would like to create a query that lets me search on the view for the item name. Right now it works for searching npc names.

$query = Npc::query()->where('combat_level', '>=', '1')->has('drops')->with('drops.item')->orderBy('name', 'asc');

if (request('search')) {
    $query->where('name', 'LIKE', '%'.request('search').'%');
    // add an orWhere here possibly for where drops.item.name like request search string
}

if (request()->has(['field', 'direction'])) {
    $query->orderBy(request('field'), request('direction'));
}

return Inertia::render('Bestiary', [
    'npcs' => $query->paginate()->withQueryString(),
    'filters' => request()->all(['search', 'field', 'direction'])
]);

I think I need to add in an orWhere() for the drops.item.name, but I'm not sure how to do that :/ Perhaps I can change or add to my npc model to help with this?

(also, do my relationships look correct for each model?)

quaint belfry
#

You're looking for whereHas

static pulsar
#

oh thank you. This seems to work:

if (request('search')) {
    $query->where('name', 'LIKE', '%'.request('search').'%');
    $query->orWhereHas('drops', function ($drops) {
        $drops->whereHas('item', function ($item) {
            $item->where('name', 'LIKE', '%' . request('search') . '%');
        });
    });
}