Laravel动态MLM层级佣金分配优化求助

Hey there! Let's turn that hardcoded MLM commission logic into a clean, dynamic solution that lets you adjust the number of levels (5/10 or any number) with just a config change. Here's a scalable, maintainable approach:

1. Add Configurable Settings #

First, create a dedicated config file to store your MLM settings so you don't have to touch code to adjust levels or commission rates.

Create config/mlm.php:

return [
    // Max commission levels (change this to 5 or 10 as needed)
    'max_levels' => 10,
    
    // Commission percentage per level (adjust these to your needs)
    'level_rates' => [
        1 => 5,   // Level 1: 5% commission
        2 => 3,   // Level 2: 3%
        3 => 2,   // Level 3: 2%
        4 => 1,
        5 => 0.5,
        // Add up to max_levels entries
    ]
];

2. Dynamic Hierarchy Traversal (Basic Loop) #

Replace your hardcoded 10-level logic with a loop that runs up to the configured max levels. This is straightforward and easy to follow:

// Get config values
$maxLevels = config('mlm.max_levels');
$orderAmount = $request->order_amount; // Assume this is your transaction amount
$currentCustomerId = $request->customers_id;

$commissionRecords = [];

for ($level = 1; $level <= $maxLevels; $level++) {
    // Fetch the current customer's sponsor
    $sponsorId = DB::table('customers')
        ->where('customers_id', $currentCustomerId)
        ->value('sponsor_id'); // Adjust this field name to match your schema
    
    // Stop early if there's no more sponsors in the chain
    if (!$sponsorId) {
        break;
    }

    // Get commission rate for this level (default to 0 if not set)
    $rate = config("mlm.level_rates.$level", 0);
    $commission = $orderAmount * ($rate / 100);

    // Prepare record for commission table
    $commissionRecords[] = [
        'customer_id' => $sponsorId,
        'level' => $level,
        'commission_amount' => $commission,
        'order_id' => $request->order_id, // Link to your order record
        'created_at' => now(),
        'updated_at' => now()
    ];

    // Move up the hierarchy for next iteration
    $currentCustomerId = $sponsorId;
}

// Bulk insert all commission records (more efficient than individual inserts)
if (!empty($commissionRecords)) {
    DB::table('mlm_commissions')->insert($commissionRecords);
}

3. Optimized Query with Recursive CTE (Better Performance) #

If you're dealing with large datasets, the loop above will run N separate queries (one per level). For better performance, use a recursive CTE to fetch all sponsors in the hierarchy in a single query:

$maxLevels = config('mlm.max_levels');
$orderAmount = $request->order_amount;

// Fetch full sponsor hierarchy in one query
$sponsorHierarchy = DB::select("
    WITH RECURSIVE sponsor_chain AS (
        SELECT customers_id, sponsor_id, 1 AS level
        FROM customers
        WHERE customers_id = ?
        UNION ALL
        SELECT c.customers_id, c.sponsor_id, sc.level + 1
        FROM customers c
        JOIN sponsor_chain sc ON c.customers_id = sc.sponsor_id
        WHERE sc.level < ?
    )
    SELECT sponsor_id, level
    FROM sponsor_chain
    WHERE sponsor_id IS NOT NULL
    ORDER BY level ASC
", [$request->customers_id, $maxLevels]);

// Calculate commissions and prepare records
$commissionRecords = collect($sponsorHierarchy)->map(function ($item) use ($orderAmount) {
    $rate = config("mlm.level_rates.$item->level", 0);
    return [
        'customer_id' => $item->sponsor_id,
        'level' => $item->level,
        'commission_amount' => $orderAmount * ($rate / 100),
        'order_id' => $request->order_id,
        'created_at' => now(),
        'updated_at' => now()
    ];
})->toArray();

// Bulk insert
if (!empty($commissionRecords)) {
    DB::table('mlm_commissions')->insert($commissionRecords);
}

Key Benefits #

• Fully Configurable: Just update config/mlm.php to change max levels or commission rates—no code edits needed.
• Scalable: Works for any number of levels (not just 5 or 10).
• Maintainable: Clean, readable code that's easy to debug or extend later.
• Performance: The recursive CTE approach cuts down on database queries, which is crucial for large MLM networks.

Note: Adjust field names (like sponsor_id, order_amount, or table names) to match your actual database schema.

内容的提问来源于stack exchange，提问作者Vinod Vishwakarma