droirqk4795 2018-02-09 10:53
浏览 235
已采纳

在where子句中使用FIND_IN_SET的Yii2 Active Record

Product Table Structure and data -

id, product_name, hsn, product_category, product_subcategory, company, pic, part_no, min_stock, stock, rate, notes

The product_subcategory column contains the values like for eg.

13,14,31,138
18
126,140,176,177
78,79

The above numbers are ids of the following table

product_subcategory - id, subcategory_name

While creating an Invoice I need to search the product by the combination of product_category, product_subcategory, company.

For this, I have the ajax as follows

<?php
$script2 = <<< JS
$(document).ready(function () {
$(document.body).on('change', '#purchaseitems-0-category_id, #purchaseitems-0-model_id, #purchaseitems-0-company_id', function () {

    var tt = $("#purchaseitems-0-category_id").val();
    var tt2 = $("#purchaseitems-0-model_id").val();
    var tt3 = $("#purchaseitems-0-company_id").val();

    var stuff1 ={'key1': tt ,'key2': tt2, 'key3': tt3};     
    p1();
});
});

function p1() {
        var stuff ={'key1':$("#purchaseitems-0-category_id").val(),'key2':$("#purchaseitems-0-model_id").val(), 'key3': $("#purchaseitems-0-company_id").val()};

        $.ajax({
            type: "POST",
            url: "http://localhost/yii-application/backend/web/index.php?r=purchase/p1",
            data: {result:JSON.stringify(stuff)},
            success: function (test4) {

            var json_obj5 = $.parseJSON(test4);

            $('#purchaseitems-0-name_of_product').val(json_obj5.id);
            $('#purchaseitems-0-hsn').val(json_obj5.hsn);
            $('#purchase-taxrate').val(json_obj5.rate);
            $('#purchaseitems-0-part').val(json_obj5.part_no);
                    },
                    error: function (exception) {
                        alert(exception);
                    }                   

                });
    }
 JS;
  $this->registerJs($script2);
 ?>

Now the real code which needs to be changed -

public function actionP1()
    {
        $data2 = Yii::$app->request->post('result');
        $data    = $_POST["result"];
        $data    = json_decode("$data", true);
        if (isset($data)) {
            $test = $data;
            $modelfedbkshiprate = \backend\models\Product::find()->where(['product_category' => $data["key1"]])->andWhere(['like' , 'product_subcategory' , $data["key2"]])->andWhere(['company' => $data["key3"]])->one();
        } else {
            $test = "Ajax failed";
        }
        return \yii\helpers\Json::encode($modelfedbkshiprate);          
    }

The issue with the above query is the like operator in product_subcategory. The like operator searches the id 7 instead of 78 and therefore I end up getting a wrong product. I thought of using FIND_IN_SET but don't know how.

Please suggest an alternate solution to like operator.

  • 写回答

2条回答 默认 最新

  • dongse5528 2018-02-10 13:18
    关注

    You need to use FIND_IN_SET to achieve this, as I understood you have the comma-separated values in the product_subcategory field and you want to get the row which has that specific id that is passed via the form to your SQL query.

    So you need to use the \yii\db\Expression inside the where clause like below.

    \backend\models\Product::find()
    ->where(['product_category' => $data["key1"]])
    ->andWhere(new \yii\db\Expression('FIND_IN_SET(:cat_to_find,product_subcategory)'))
    ->andWhere(['company' => $data["key3"]])
    ->addParams([':cat_to_find' => $data["key2"]])
    ->one();
    

    Your action should look like this

    public function actionP1()
        {
            $data2 = Yii::$app->request->post('result');
            $data    = $_POST["result"];
            $data    = json_decode("$data", true);
            if (isset($data)) {
                $test = $data;
                $modelfedbkshiprate = \backend\models\Product::find()->where(['product_category' => $data["key1"]])->andWhere(new \yii\db\Expression('FIND_IN_SET(:cat_to_find,product_subcategory)'))->andWhere(['company' => $data["key3"]])->addParams([':cat_to_find' => $data["key2"]])->one();
            } else {
                $test = "Ajax failed";
            }
            return \yii\helpers\Json::encode($modelfedbkshiprate);          
        }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 怎么在stm32门禁成品上增加记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 解riccati方程组