How to access a many-to-many relationship?

[kevinrueda]

I have two models Product and Branch, both have their relationship defined with belongsToMany and I follow the Laravel conventions where the pivot table is called branch_product and it contains an is_available field. How can I use all the product fields in the datatable and add the extra is_available? Thank you. Sorry for my English

public function builder(): Builder
    {
        $branch = Session::get('branch')->id;
        return Product::query()->select('*')
            ->whereHas('branches', function ($query) use ($branch) {
                $query->where('branches.id', $branch);
            });
    }

[lrljoe]

Hi @kevinrueda

Three elements here:

1. Avoid using "select" in your builder() method, as it will conflict with the internal mechanism that selects the columns for you.

Once that's resolved:

2. Keep in mind that you will need to define them as a "label" Column, e.g, this implodes the "name" of the branch, with commas:

Column::make('Branches')
    ->label(fn ($row) => $row->branches->pluck('name')->implode(', ')),

3. Using with will forcibly load the relation for you, avoid defining a variable where you don't need to, so long as you're certain it is defined, and restrict the "with" to only return the fields you need (e.g. id, name).

public function builder(): Builder
{
  return Product::with(['branches:id,name' => function (Builder $query) {
      $query->where('branches.id', Session::get('branch')->id);
  }]);
}

[lrljoe]

Is branches defined as a hasMany or as a hasOne in your Model?

[kevinrueda]

class Product extends Model
{
    ...
    public function branches()
    {
        return $this->belongsToMany(Branch::class)->withPivot('is_available', 'have_stock', 'minimum_stock', 'current_stock');
    }
}


class Branch extends Model
{
   ...
    public function products()
    {
        return $this->belongsToMany(Product::class);
    }
}

[kevinrueda]

A product can belong to many branches but not more than once to one branch.

[lrljoe]

Okay, so, just so I'm clear in my head about how your relationships are formed here:

Product
hasMany Branches

But, in this case, you're restricting the Branches to only the BranchID that has been provided?

Then you want to use the PivotValue of "is_available"?

[kevinrueda]

Yes, it is restricted only by the branch ID. Product 1 can be associated with Branch 1, 2, 3 ... but it cannot be associated with a branch more than once, regardless of the other fields in the pivot table