Eloquent Model relationship does not return sub-relationship specific column value

Question

I have attempted to achieve to pull the user's info and the product details (Which specified the nickname and name) when the product name equal to "Oops" but I have no idea why the getProducts does not return any things.

User Model

 public function getProducts(){
        return $this->hasMany('App\Models\Product','users_id');
    }

Product Model

public function users(){
        return $this->belongsTo(User::class);
    }

The code of pulling data:

$products = User::with(['getProducts' => function($query){
            $query->select("users_id","name","nickname");
        }])->get();

The current output:

"data": [
    {
      "id": 1,
      "name": "John Smith",
      "email": "john.smith@hotmail.com",
      "email_verified_at": null,
      "created_at": "2021-04-08T13:29:13.000000Z",
      "updated_at": "2021-04-08T13:29:13.000000Z",
      "role": 0,
      "get_products": [
        
      ]
    },
    {
      "id": 2,
      "name": "Kelvin Ooi",
      "email": "kelvin.ooi@hotmail.com",
      "email_verified_at": null,
      "created_at": "2021-04-08T13:29:13.000000Z",
      "updated_at": "2021-04-13T12:07:11.000000Z",
      "role": 1,
      "get_products": [
            {
                "nickname":"MCD",
                "name":"Oops"
            },
            {
                "nickname":"Mary Brown",
                "name":"Oops"
            },
            {
                "nickname":"Kentucy",
                "name":"KFC"
            },
            {
                "nickname":"Texas Chicken",
                "name":"TXS"
            }            
      ]
    }
  ]

The expected output

"data": [
    {
      "id": 1,
      "name": "John Smith",
      "email": "john.smith@hotmail.com",
      "email_verified_at": null,
      "created_at": "2021-04-08T13:29:13.000000Z",
      "updated_at": "2021-04-08T13:29:13.000000Z",
      "role": 0,
      "get_products": [
        
      ]
    },
    {
      "id": 2,
      "name": "Kelvin Ooi",
      "email": "kelvin.ooi@hotmail.com",
      "email_verified_at": null,
      "created_at": "2021-04-08T13:29:13.000000Z",
      "updated_at": "2021-04-13T12:07:11.000000Z",
      "role": 1,
      "get_products": [
            {
                "nickname":"MCD",
                "name":"Oops"
            },
            {
                "nickname":"Mary Brown",
                "name":"Oops"
            }
      ]
    }
  ]

Answer 1

getProducts is not a good name for the relationship, lets simply call it products.

public function products()
{
    return $this->hasMany('App\Models\Product','users_id');
}

Your code in the comment doesn't work because you specify the where clause in the main query, not the sub query.

return User::with(['products' => function ($query) { 
    $query->select("users_id","name","nickname"); 
}])
// This got to be in the sub query.
->where("products.name","Oops")
->get();

So let's update your code to this:

$productName = 'Oops';

return User::with(['products' => function ($query) use ($productName) { 
    $query->select("users_id","name","nickname", "price")
        ->where("name","LIKE", "%{$productName}%"); 
}])
->get();

I have seen your comment to this answer. Let's define a total custom attribute for the User model:

class User extends Model
{
    protected $appends = ['total'];

    public function getTotalAttribute()
    {
        // This is higher order message, if you haven't used it: https://laravel.com/docs/8.x/collections#higher-order-messages
        return $this->products->sum->price;
    }
}

Then the total attribute will be part of any user.