How to use where for nested relationship in laravel controller.

Product::with('productstore.entitystore.entity')
->where('account_id',$account_id)->get();

product table

products_store table (one product has many store)

store table (one store has one entity / one entity has multiple store)

entity table (One entity has one account / one account has multiple entity)

In Product Model

public function productstore()
{
   return $this->hasMany('App\Models\ProductStore','product_id');
}

In ProductStore Model

public function entitystore()
{
    return $this->hasMany('App\Models\Store','id');
}

In Store Model

public function entity()
{
        return $this->belongsTo('App\Models\Entity','entity_id','id');
}

Here I am getting error for account_id

Column not found: 1054 Unknown column 'account_id' in 'where clause' 

Can some one suggest to solve this and is the way of relationship am using is correct.

CodePudding user response：

If your goal is to load all products containing a link to entities with account_id = $account_id, you need something like this:

$products = Product
    ::with([
        'productstore.entitystore.entity' => function($query) use($account_id) {
            $query->where('account_id', $account_id);
        }
    ])
    ->whereHas('productstore.entitystore.entity', function($query) use($account_id) {
        $query->where('account_id', $account_id);
    })
    ->get();

whereHas accepts the name of the relationship(link) as first argument, and a callback function in the second argument. It will now only load the products that have an entity with an account_id you provided.

You can see that I'm also performing the same query in with to ensure you only have the valid entities loaded with the products that come up.