Laravel Pivot Relationship not unique table/alias

Question

I have three Tables & corresponding models:

Table ------ Model

papers ------ Paper

paper_stocks ------ PaperStock

paper_stock_amounts ------ PaperStockAmount

Paper contains diff. types of paper. PaperStock contains different places where these papers can be stored PaperStockAmount contains the paper id, the stock id and the amount, to determine how many of paper A are in stock B.

Now I want to check outgoing from the paper how manyy are in each stock. Therefore Im doing this in my view:

@foreach($papers as $paper)
    @foreach($paper->paperStockAmount as $ps)
        <tr>
            <td>{{ $ps->stock->name }}</td>
            <td>{{ $ps->amount }}</td>
        </tr>
    @endforeach
@endforeach

This should show every type of paper and in which stocks along with the amount they are in.

However this is the error:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'paper_stock_amounts' (SQL: select `paper_stock_amounts`.*, `paper_stock_amounts`.`id` as `pivot_id`, `paper_stock_amounts`.`amount` as `pivot_amount` from `paper_stock_amounts` inner join `paper_stock_amounts` on `paper_stock_amounts`.`id` = `paper_stock_amounts`.`id` where `paper_stock_amounts`.`id` in (1, 2))

The relationships are defined like this:

Paper

class Paper extends Model
{
    public function paperStockAmount()
    {
        return $this->belongsToMany('App\PaperStockAmount', 'paper_stock_amounts')->withPivot('amount');
    }
}

PaperStockAmount

class PaperStockAmount extends Model
{
    public function paper()
    {
        return $this->hasOne('App\Paper', 'id', 'paper_id');
    }

    public function stock()
    {
        return $this->hasOne('App\PaperStock', 'id', 'stock_id');
    }
}

Why doesn't this work?

Answer 1

You've defined M-M Relationship between paper & paper_stock.

i.e. If you are fetching paperStockAmount from $paper, then you would get paper_stock results with the pivot column amount.

You should do it like this:

@foreach($papers as $paper)
    @foreach($paper->paperStockAmount as $paper_stock)
        <tr>
            <td>{{ $paper_stock->name }}</td>
            <td>{{ $paper_stock->pivot->amount }}</td>
        </tr>
    @endforeach
@endforeach

Use pivot to fetch the pivot column named - amount

Hope this helps!

Answer 2

I am not sure but it looks your relation from Paper to PaperStockAmount is has-many, so your relation should be as:

Paper

class Paper extends Model
{
    public function paperStockAmount()
    {
        return $this->belongsToMany('App\PaperStockAmount');
    }
}

Then your view code will work.

OR

If the relation between from Paper and PaperStock is many-to-many then your relation should be as:

Paper

class Paper extends Model
{
    public function paperStockAmount()
    {
        return $this->hasMany('App\PaperStock', 'paper_stock_amounts')->withPivot('amount');
    }
}

Then in your view, it can be accessed as: (taking from @saumya answer)

@foreach($papers as $paper)
    @foreach($paper->paperStockAmount as $paper_stock)
        <tr>
            <td>{{ $paper_stock->name }}</td>
            <td>{{ $paper_stock->pivot->amount }}</td>
        </tr>
    @endforeach
@endforeach