Issue on sum table total sql codeigniter

问题:

I have a little problem when I want to make the total sum of the column by id.

Table products

id   code    name        price  quantity
522  123     Product 1     12      4

Table purchase_items

id  product_id  quantity
1      522            5
2      522            1

Table sale_items

id  product_id  quantity
1      522            1
2      522            1

function codeigniter:

$products = $this->alerts->select('products.id as productid, products.code as code, products.name, products.price, sum(sale_items.quantity)')
      ->from('products');
      $this->alerts->join('sale_items', 'products.id = sale_items.product_id');           
      $this->alerts->group_by("products.id");

      $products = $this->alerts->select('products.id as productid, products.code as code, products.name, products.price, sum(purchase_items.quantity)')
      ->from('products');
      $this->alerts->join('purchase_items', 'products.id = purchase_items.product_id');           
      $this->alerts->group_by("products.id");


        $this->alerts->unset_column('productid');

        foreach ($this->result_array() as $product)
        {
            $products[] = $product['record'];
        }

        return $products;
    }

The issue is that the result for

purchase_items the result is 12
sale_items      the result is 4

Why is taking the result x 2 ?

Any help is appreciated.

回答1:

Because you are joining purchasing activity to sales activity via your item master (products table). As a result, the number of rows for purchasing that are included in the sum depend on how many rows in sales you have; and vice versa for sales.

You need to separate those queries either as separate SQL statements that you combine in the view, or you can use a SQL UNION ALL to combine two statements: one for purchases and one for sales.

Two statements:

$this->alerts
            ->select('products.id as productid, products.code as code, products.name, products.price, sum(purchase_items.quantity)')
            ->from('products');
            $this->alerts->join('purchase_items', 'products.id = purchase_items.product_id');
                $this->alerts->group_by("products.id");
            echo "Total Purchases";
            echo $this->alerts->generate();


 $this->alerts
            ->select('products.id as productid, products.code as code, products.name, products.price, sum(sale_items.quantity)')
            ->from('products');
            $this->alerts->join('sale_items', 'products.id = sale_items.product_id');           
                $this->alerts->group_by("products.id");
            echo "Total Sales";
            echo $this->alerts->generate();

Or, in SQL:

$results = $this->db->query('
    SELECT products.id as productid, products.code as code, products.name, products.price, ''Purchase'' as transaction, sum(purchase_items.quantity)
    FROM products
    JOIN purchase_items ON products.id = purchase_items.product_id
    GROUP BY products.id
    UNION ALL
    SELECT products.id as productid, products.code as code, products.name, products.price, ''Sale'' as transaction sum(sales_items.quantity)
    FROM products
    JOIN sales_items ON products.id = sales_items.product_id
    GROUP BY products.id')->result_array();

// Echo $results....

Edit: I'm not entirely sure what you have in your Alerts model, but in your revised query you are assigning a SQL statement twice to the variable $products but not executing the statements?

Also make sure your Alerts model extends CI_Model.

In more standard Codeigniter syntax, I would do something like this:

    // In Controller, assuming the Alerts model is loaded, and using method chaining.

    // Get all sales. By the way, you normally have to group on all columns *not* being aggregated. You might
    // get unexpected results by grouping on price, unless the price never changes.
    $this->alerts->select('products.id as productid, products.code, products.name, products.price, sum(sale_items.quantity) as quantity')
          ->from('products')
          ->join('sale_items', 'products.id = sale_items.product_id')
          ->group_by('products.id, products.code, products.name, products.price');
    $sales = $this->alerts->get()->result_array();

    // Get all purchases
    $this->alerts->select('products.id as productid, products.code, products.name, products.price, sum(purchase_items.quantity) as quantity')
          ->from('products')
          ->join('purchase_items', 'products.id = purchase_items.product_id')
          ->group_by(products.id, products.code, products.name, products.price);
    $purchases = $this->alerts->get()->result_array();

    // Put it all together
    $products = array_merge($sales, $purchases);

    // And now do something with your combined results $products. Send to view, echo etc.