How do I show multiple value/field from a foreign key?-CodePudding

Okay so I don't know how to word my question, so excuse me if it doesn't make sense

My view

@forelse ($transaction as $key=>$value)
                        <tr>
                            <td class="text-center">{{$key   1}}</th>
                            <td>{{$value->transaction_item->item->item_name}}</td>
                            <td>{{$value->transaction_item->quantity}}</td>
                            <td>{{$value->transaction_item->price}}</td>
                            <td>{{$value->total_price}}</td>
                            <td>{{$value->created_at}}</td>
                            <td>{{$value->updated_at}}</td>
                            <td>{{$value->last_updated_by}}</td>
                        <tr>
@endforelse

My controller

$transaction = Transaction::all();
return view('transaction.index', compact('transaction'));

My model

    // Transaction
    protected $table = 'transaction';
    protected $guarded = [];

    public function transaction_item()
    {
        return $this->hasOne(TransactionItem::class, 'transaction_id');
    }

    // TransactionItem
    protected $table = 'transaction_item';
    protected $guarded = [];
    public $timestamps = false;
    
    public function transaction()
    {
        return $this->belongsTo(Transaction::class, 'transaction_id');
    }
    public function item()
    {
        return $this->belongsTo(MasterItem::class, 'master_item_id');
    }

Transaction has TransactionItem that has multiple item, quantity, and price. How do I show that to my blade view? When I just do {{$value->transaction_item->item->item_name}}, it only show the first item and also the same for quantity and price. I tried changing item relationship in my TransactionItem to belongsToMany but then this error popped up

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'myDatabase.master_item_id' doesn't exist (SQL: select `master_item`.*, `master_item_id`.`transaction_item_id` as `pivot_transaction_item_id`, `master_item_id`.`master_item_id` as `pivot_master_item_id` from `master_item` inner join `master_item_id` on `master_item`.`id` = `master_item_id`.`master_item_id` where `master_item_id`.`transaction_item_id` = 3)

I'm not sure what causes that problem but, I dont have table or column named transaction_item_id that's for sure, my table transaction_item primary key is id

Here's my table structure

transaction: id(pk), total_price
transaction_item: id(pk), transaction_id(fk), master_item_id(fk), quantity, price
master_item: id(pk), item_name, price

CodePudding user response：

you need to tell us about your tables structure first , table names , foreign keys and how they are related ... something doesn't add up

you said TransactionItem that has multiple items , but in your relation you have TransactionItem belongs to items

also in belongsToMany second argument is the table name not the foreign key

so if you have this table structure

transactions : id , total_price
items : id , item_name , item_price 
transaction_items : id ,transaction_id , item_id

in this scenario transaction_items is a pivot table you can search and read about it basically it only connects 2 table together

you dont need to add relation to pivot table model , you can directly define relation between 2 tables (transactions , items ) just by mentioning their pivot table your relation should be

// Transaction

public function items()
{
    return $this->belongsToMany( Item::class, 'transaction_items' , 'transaction_id' , 'item_id' );
}

in view

@foreach($transactions as $key=>$transaction)

   {{$transaction->total_price}}

   @foreach($transaction->items as $item )
     {{$item->item_name}}
   @endforeach
                         
@endforeach

------------------------------ edit-----------------------

usually pivot table only contains the id of other 2 other table so it doesn't have any useful data in it , it only works as a bridge between transactions and items

transaction -> transaction_item -> items

scenario happens in many to many relation (if its 1 to many or 1 to 1 relation you can just store the idof table 1 directly in other table 2 )

so when you define belongsToMany relation you directly connect 2 main tables together and mention the name of pivot table basically it goes like

transaction belongs to many items via transaction_item

// Transaction

public function items()
{
    return $this->belongsToMany( Item::class, 'transaction_items' );
}

but you have stored other data apart from foreign keys (id) in the transaction_item table so this wont work for you ... you need direct relation to transaction_item if you want to show quantity, price

so in your case u should define

// Transaction

public function transaction_item()
{
    return $this->hasMany(TransactionItem::class, 'transaction_id');
}


// TransactionItem

public function item()
{
    return $this->belongsTo(TransactionItem::class, 'item_id');
}

in view

@foreach($transactions as $key=>$transaction)

   {{$transaction->total_price}}

   @foreach($transaction->transaction_item $transaction_item)
     {{$transaction_item->price}}
     {{$transaction_item->quantity}}
     {{$transaction_item->item->item_name}}
   @endforeach

@endforeach