Relationship invoices between 5 tables-CodePudding

I have table called invoices, orders, games, products, users

This is my invoices table structure

id name order_id

This is my orders table structure

id name game_id product_id user_id

This is my games table structure

id name display_name

This is my products table structure

id product_sku name price profit

As you see in this case I'm trying to make an order that generate an invoices after you place an order. In the invoices, I wanted to show the games name, products name, price, and user who make the order. What relationship should I use? is it hasMany, or belongsToMany? or should I make another table called invoice_order?

Updated!

I forgot to show my table game_product, I already make a belongsToMany relationship between games and products table.

id game_id product_id

Cmiiw

CodePudding user response：

A simple belongs to relation is good enough for your case scenario

Invoice.php

public function order(){
    return $this->belongsTo(Order::class);
}

Order.php

public function game(){
    return $this->belongsTo(Game::class)->select('id', 'name')
}
public function product(){
    return $this->belongsTo(Product::class)->select('id', 'name')
}

Controller.php

Invoice::with(['order' => function($q) {
    return $q->with('game', 'product');
})->get();

CodePudding user response：

I think you should have the following table structure:

Orders (renamed from invoice)

id name user_id

order_product (pivot)

order_id product_id

games

id name display_name

products

id product_sku name price profit game_id

These should be the relationships:

Order model

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

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

Game model

public function product() { 
     return $this->hasMany(Product::class);
}
public function orders() { 
     return $this->hasManyThrough(Order::class, Product::class);
}

Product model

public function game() { 
     return $this->belongsTo(Game::class);
}
public function orders() {
    return $this->belongsToMany(Order::class);
}

This way you can generate an invoice:

$invoiceData = Order::with([ 'user', 'products', 'products.game' ])->find($id);

This will have all necessary information of the order within the $invoiceData object e.g. $invoiceData->user will be the user placing the order and $invoiceData->products will be a collection of ordered products.

Note that it's usually good practice to put the price in the order_product pivot as an extra pivot field since the price people buy an item for is not always the price that the item goes for at all times so that way you can have information on the price the item was sold for rather than what item was sold and how much it costs today.