Searching collection records by relationship attributes-CodePudding

I have a game where people can get some items and equip them.

The items data is placed in two tables that are in relationship.

Items table contains all the possible items and user_items table contains the items that are owned by a player.

user_items table: id | user_id | item_id | is_equipped

<?php

namespace App\Models;

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

class Item extends Model
{
    use HasFactory;

    public function userItems()
    {
        return $this->belongsTo(UserItem::class);
    }
}

items table: id | item_name | body_part

<?php

namespace App\Models;

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

class UserItem extends Model
{
    use HasFactory;

    public function items()
    {
        return $this->hasOne(Item::class, 'id', 'item_id');
    }
}

Now I am getting a collection of the user's items $userItems = UserItem::where('user_id', Auth::id())->get(),

How do I search this collection by related table's columns? For example I want to get user $userItems where is_equipped == 1 and body_part == "head".

CodePudding user response：

What you need is filter by the relation like this:

$userItems = UserItem::where('user_id', Auth::id())->whereHas('items', function($q)
{
    $q->where('is_equipped', '=', 1);

})->get();

CodePudding user response：

You can use the Eloquent's relationships to search the collection by related table's columns.

To get the user's items where is_equipped == 1 and body_part == "head", you can use the following code:

$userItems = UserItem::where('user_id', Auth::id())
->whereHas('items', function ($query) {
    $query->where('is_equipped', 1)->where('body_part', 'head');
})->get();

This code first queries the user_items table for all items that belong to the user. Then, it uses the whereHas method to filter the results based on the related items table's columns. The closure passed to whereHas receives a $query variable that is a instance of Query Builder that you can use to filter the items table.

You could also use the join method to join the items table to the user_items table and then filter by the columns in the items table:

 $userItems = UserItem::join('items', 'items.id', '=', 'user_items.item_id')
    ->where('user_items.user_id', Auth::id())
    ->where('items.is_equipped', 1)
    ->where('items.body_part', 'head')
    ->get();

This will give you a collection of user_items that are owned by the user and have is_equipped = 1 and body_part = 'head' in the items table.