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

Question

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.

Answer 1

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);          
    }