How to sell items as a set of items with specific name in Laravel

I build a database, after finished and start working, there is a problem arise from the seller, the seller is selling items, so the seller required to sell an items as a set. A set contains specific items with specific Quantities that are known for instance: a set has a name like AYZ and it has to have quantities like 1,2,..etc so if the buyer wants to buy AYZ 2sets, the buyer knows how many items each AYZ will have. So instead of mentioning all these items simply the buyer will say "I need AYZ 2sets" and that has even specific price not all item price. the seller will tell each AYZ price like: $100x2sets = $200total. but the seller knows that $100 will divide to all other items that a set will contain. So I want to be able to make AYZ set and specify how many items it will have with specific quantities like : AYZ One set contains:

A = 2pcs
Y = 3pcs
Z = 5pcs so if the buyer wants AYZ 2set, it will multiply by 2 and will be like this: AYZ Two set contains:
A = 2pcs*2 = 4pcs
Y = 3pcs*2 = 6pcs
Z = 5pcs*2 = 10pcs This is my database I am using right now, the seller is able to sell each item as an individual, SO I want to able to add this functionality to my database without changing database tables because the system is working right now (production mode). I have these tables:
SoldInventory Table,

class SoldInventory extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['customer_id','store_id','date_of_sale','tracking_no','reference','reference_no','description','user_id'];

    public function soldInventoryItems()
    {
        return $this->hasMany(SoldInventoryItems::class);
    }

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }

    public function salesInvoice()
    {
        return $this->hasOne(SalesInvoice::class);
    }

    public function store()
    {
        return $this->belongsTo(Store::class);
    }
}

SoldInventoryItems Table.

class SoldInventoryItems extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['inventory_in_store_id','quantity','unit_price','sub_total'];


    public function soldInventory()
    {
        return $this->belongsTo(SoldInventory::class);
    }

    public function inventoryInStore() : BelongsTo
    {
        return $this->belongsTo(InventoryInStore::class);
    }
}

InventoryInStore Table.

class InventoryInStore extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['inventory_item_id','quantity','store_id'];

    public function store()
    {
        return $this->belongsTo(Store::class);
    }

    public function inventoryItem()
    {
        return $this->belongsTo(InventoryItem::class);
    }

    public function soldInventoryItems()
    {
        return $this->hasMany(SoldInventoryItems::class);
    }
}

InventoryItem Table

class InventoryItem extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    public function item()
    {
        return $this->belongsTo(Item::class);
    }

    public function category()
    {
        return $this->belongsTo(Category::class);
    }


    public function inventoryInStores()
    {
        return $this->hasMany(InventoryInStore::class);
    }
}

I am using laravel and livewire.

LaryAI

2 months ago

Level 58

Certainly! Since you cannot change the database tables, the best approach is to define the concept of a "Set" (like "AYZ") as configuration or code (an array or collection in Laravel), and handle the logic for selling a set within your application layer. When a set is sold, you will expand it into its component items and store them as regular SoldInventoryItems, just as if each item had been sold individually, but using the "Set" as an abstraction to make the seller/buyer's life easier.

Here’s how you can implement this:

1. Define Your Sets (Programming-Level Only)

For example, you can define your set structures in a config file, or in code:

// config/item_sets.php
return [
    'AYZ' => [
        'name' => 'AYZ',
        'items' => [
            // inventory_item_id => quantity_per_set
            1 => 2, // A
            2 => 3, // Y
            3 => 5, // Z
        ],
        'set_price' => 100,
    ],
    // More sets...
];

2. Selling a Set (Application Logic Example)

When a seller sells a set, you expand the set into its items, multiply the quantities by number of sets, and create SoldInventoryItems for each:

use Illuminate\Support\Facades\DB;

function sellSet($soldInventoryId, $setKey, $numberOfSets)
{
    $set = config("item_sets.$setKey");
    if (!$set) {
        throw new Exception("Set not found");
    }

    $totalSetPrice = $set['set_price'] * $numberOfSets;

    // Calculate total quantity per item and allocate price per item
    $items = $set['items'];
    $totalItemsQty = collect($items)->map(fn($qty) => $qty * $numberOfSets);

    // Optionally split the price per item, here simply dividing equally:
    $totalItemsCount = $totalItemsQty->sum();
    // Or distribute proportionally to item count
    $unitPricePerItem = $totalSetPrice / $totalItemsCount;

    DB::transaction(function () use (
        $soldInventoryId, $items, $numberOfSets, $unitPricePerItem, $set
    ) {
        foreach ($items as $inventory_item_id => $qtyPerSet) {
            $quantity = $qtyPerSet * $numberOfSets;

            // Retrieve InventoryInStore for the relevant InventoryItem
            $inventoryInStore = \App\Models\InventoryInStore::where('inventory_item_id', $inventory_item_id)
                ->firstOrFail();

            \App\Models\SoldInventoryItems::create([
                'sold_inventory_id'     => $soldInventoryId,
                'inventory_in_store_id' => $inventoryInStore->id,
                'quantity'              => $quantity,
                'unit_price'            => $unitPricePerItem,
                'sub_total'             => $quantity * $unitPricePerItem,
            ]);
        }
    });
}

3. Usage (For Example in Your Livewire Component)

// Example: Buyer wants to buy 2 AYZ sets and the SoldInventory ID is 5
sellSet(5, 'AYZ', 2);

Notes:

This solution leaves your database schema unchanged, but allows the application to deal with Sets as higher-order groups of items.
You can place the set definitions in a config file, or even in the database (if you add new tables later), but for now, config or code is fastest for production systems.
Pricing per item inside the set is split equally. Adjust the price distribution logic as necessary for your needs.

Summary:
Define your sets in config/code, expand to items in your app, and process sales as if individual items were sold, but powered by the "set" logic. This avoids any database changes and works with your existing SoldInventory/SoldInventoryItems models.

yonka

2 months ago

Level 2

What about if we change database? because this solution will require to change price as a code and that is not suitable

Please or to participate in this conversation.