mysql - Codeigniter数据表多行选择不同价格的同一商品

Question

我有一个 codeigniter 项目，用于管理元素的购买和问题。表格如下:

商店商品

+---------+-----------+
| item_id | item_name |
+---------+-----------+
|       1 | Pen       |
|       2 | A4        |
|       3 | Ruler     |
|       4 | Stapler   |
|       5 | A3        |
+---------+-----------+

store_update_stock

+-----------------+------------+---------------+-------------+
| update_stock_id | invoice_no | prchased_date | order_state |
+-----------------+------------+---------------+-------------+
|               1 | A0001      | 2019-10-05    | purchase    |
|               2 | A0002      | 2019-10-06    | purchase    |
|               3 | B0003      | 2019-10-07    | issue       |
+-----------------+------------+---------------+-------------+

store_update_stock_details

+-------------------------+-----------------+------+-----+------------+
| update_stock_details_id | update_stock_id | item | qty | unit_price |
+-------------------------+-----------------+------+-----+------------+
|                       1 |               1 |    1 |  50 |      10.00 |
|                       2 |               1 |    4 |  10 |     250.00 |
|                       3 |               2 |    1 | 100 |      10.50 |
|                       4 |               3 |    1 |   2 |      10.00 |
|                       5 |               3 |    4 |   1 |     250.00 |
|                       6 |               3 |    1 |   4 |      10.50 |
+-------------------------+-----------------+------+-----+------------+

然后我需要在执行问题后找到项目的余额。在此示例中，所需的输出如下，并且应在我看来查看输出以再次执行问题。 (换句话说，即使是相同的商品也应该以不同的价格单独查看。

+-----------+-----+------------+
| item_name | qty | unit_price |
+-----------+-----+------------+
| Pen       |  48 |      10.00 |
| Pen       |  96 |      10.50 |
+-----------+-----+------------+

Controller

public function isExistProduct()
    {    
        $id = $this->input->get('q');
        $data = $this->Item_model->isExistProduct($id);
        if (!empty($data)) {
            echo json_encode(array('status' => true, 'data' => $data));
        } else {
            echo json_encode(array('status' => false));
        }
    }

模型(使用此查询)

    public function isExistProduct($q)
        {
            if (!empty($q)) {           

                $this->db->select("store_item.item_id, store_item.item_name, sum(qty) as qty, unit_price as up");
                $this->db->from('store_update_stock_details');
                $this->db->join('store_update_stock', 'store_update_stock_details.update_stock_id=store_update_stock.update_stock_id');
                $this->db->join('store_item', 'store_update_stock_details.item=store_item.item_id');            
                $this->db->where("store_update_stock.status=1 and store_item.item_id= $q");           
                $this->db->group_by( 'store_item.item_id','store_update_stock_details.unit_price');       
                $q1 = $this->db->get();
                if ($q1->num_rows() > 0) {
                    return $q1->result_array();
                }
                return 0;
            }
        }
Relevant parts of View
======================
<script type="text/javascript">

    $(document).on("change", "#item", function () {

        $.ajax({
            'url': '<?=site_url("item/isExistProduct/?q=")?>' + $('#item').val(),
            'method': 'GET',
            'success': function (data) {


                var jData = JSON.parse(data);
                if (jData.status == true) {

                jData.data.forEach(data => {
                    $('#request_table').append('<tr>' +
                        '<td ><span id="product" >' + jData.data[0].item_name + '</span>' +
                        '<input type="hidden" id="item_id[]" name="item_id[]" value="' + jData.data[0].item_id + '">' +
                        '</td>' +                       
                        '<td class="text-center">' + jData.data[0].qty + '</td>' +                      
                        '<td class="text-center"><input class="form-control text-right"  disabled id="sales_price[]" name="sales_price[]" value="' + jData.data[0].up+ '"></td>' +
                        '<td class="text-center"><input class="form-control text-center rquantity" data-qty-bal="' + jData.data[0].qty + '" autofocus required type="number" step="any" id="qty[]" name="qty[]" ></td>' +   
                        '<td class="text-center" ><i class="fa fa-remove remove" style="cursor: pointer"></i></td>' +
                        '</tr>');
                    })
                }

            },
            'error': function () {

            }
        });


    });
</script>

<div class="col-md-12 column">
                    <div class="col-md-12">
                        <div class="control-group table-group">
                            <label class="table-label">Issue Items *</label>

                            <div class="controls table-controls">
                                <table id="request_table"
                                       class="table items table-striped table-bordered table-condensed table-hover">
                                    <thead>
                                    <tr class="" style="background-color: #ff66a3 !important;">
                                        <th class="col-md-5">Item Name</th>
                                        <th class="text-center col-md-2">Available Qty</th>                                        
                                        <th class="text-center col-md-2">Unit Price</th>
                                        <th class="text-center col-md-2">Issuing Qty</th>

                                        </th>
                                        <th class="col-md-2" style="width: 30px !important; text-align: center;">
                                            <i class="fa fa-trash-o" style="opacity:0.5; filter:alpha(opacity=50);"></i>
                                        </th>
                                    </tr>
                                    </thead>
                                    <tbody></tbody>
                                    <tfoot>
                                    <tr id="tfoot" class="tfoot active">
                                        <th colspan="2">Total</th>
                                        <th class="text-right"></th>
                                        <th class="text-center">0</th>

                                        <th class="text-center"><i class="fa fa-trash-o"
                                                                   style="opacity:0.5; filter:alpha(opacity=50);"></i>
                                        </th>
                                    </tr>
                                    </tfoot>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="row">
                    <div class="col-md-8"></div>
                    <div class="col-md-4">
                        <button type="submit" id="add_sale" class="btn btn-primary btn-block">Issue</button>
                    </div>
                </div>

所有部件都工作正常。但 View 仅根据模型输出以下输出

谁能帮我吗？

Answer 1

当您在 group_by 函数中使用多个值时，需要将其指定为数组。例如，您可以将其添加到代码中替换此

$this->db->group_by( 'store_item.item_id','store_update_stock_details.unit_price');

与

$this->db->group_by(array( 'store_item.item_id','store_update_stock_details.unit_price'));