CodeIgniter+MySQL按产品ID汇总库存数量实现方案问询

Hey there! Let's fix this inventory aggregation issue you're facing. The main problem with your current model query is that it's pulling every individual stock entry instead of summing them up per product, and the join order might be causing unexpected results too. Here's how to get the total stock quantity for each product correctly:

Step 1: Update the Model (item_model.php) #

Replace your itemList method with this optimized version:

public function itemList() {
    $this->load->database();
    
    // Start from products table to ensure all products are included (even those with no stock)
    $this->db->select('p.*, c.category_name, s.size, b.brand_name, COALESCE(SUM(t.quantity), 0) AS quantity')
             ->from('products p')
             // Left join to keep products even if they have no matching stock entries
             ->join('stocks t', 'p.prod_id = t.product_id', 'left')
             ->join('category c', 'p.category_id = c.category_id', 'left')
             ->join('sizes s', 'p.size_id = s.size_id', 'left')
             ->join('brand b', 'p.brand_id = b.brand_id', 'left')
             // Group by unique product identifier and all non-aggregated fields
             ->group_by('p.prod_id, p.prod_code, p.prod_desc, b.brand_name, c.category_name, s.size');
    
    $result = $this->db->get()->result();
    return $result;
}

Key Changes Explained #

• Join Order & Type: We start with the products table and use left join for all associations. This guarantees every product appears in your list, even if it has no stock entries yet.
• Stock Aggregation: SUM(t.quantity) calculates the total stock for each product. COALESCE converts NULL values (for products with no stock) to 0, so you won't see empty quantity values.
• Grouping: Grouping by prod_id (and other non-aggregated product details) ensures you get one row per product with its summed total stock, instead of one row per individual stock entry.

Step 2: Verify the View #

Your existing product_view.php will work seamlessly with this update—you're already using $item->quantity which now holds the aggregated total. The table structure and action buttons don't need any changes.

Test with Your Sample Data #

Using your provided stocks records:

• Product ID 1 will show a total quantity of 12 (10 + 2)
• Product ID 3 will show a total quantity of 15 (7 + 8)

This matches exactly the output format you're expecting.

Quick Tips #

• Avoid full joins unless you explicitly need rows from both tables even when there's no match—this can lead to duplicate or empty rows. left join is the safer choice here.
• Always group by all non-aggregated columns in your SELECT statement to avoid database errors (especially with strict SQL modes enabled).

内容的提问来源于stack exchange，提问作者rash eminem