droirqk4795
2018-02-09 10:53
浏览 183

在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);          
        }
    
    已采纳该答案
    打赏 评论
  • dongwa3808 2018-07-11 10:18

    Follow this (Find_In_SET ) is working for me.

    Categories::find()->where(new Expression('FIND_IN_SET(:category_to_find, categories)'))->addParams([':category_to_find' => 3])->asArray()->all();
    
    打赏 评论

相关推荐 更多相似问题