Yii2 Active Record using FIND_IN_SET in where clause

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.

your question is not clear how come you are using `like` operator for `key3` and not for the rest of them, which is coming from the field `purchaseitems-0-model_id` and is unknown as no `HTML` added, is it a multi-select drop-down that is selecting multiple `id`'s ? — Muhammad Omer Aslam, Feb 09 '18 at 23:33
I am using like operator for key2 not key3. The reason i am using it is the product_subcategory column in product table has values - 13,14,31,138 18 126,140,176,177 78,79 but the remaining columns product_category, company has single value. — master, Feb 10 '18 at 06:21
I was trying to work with this solution https://stackoverflow.com/questions/4436591/how-can-i-search-within-a-table-of-comma-separated-values — master, Feb 10 '18 at 06:40
so you have comma-separated list of values inside the `product_subcategory` and you want to list the results if the given `product_subcategory` exists? is it what you are asking — Muhammad Omer Aslam, Feb 10 '18 at 12:23

score 2 · Accepted Answer · answered Feb 10 '18 at 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);          
    }

score 0 · Answer 2 · answered Jul 11 '18 at 10:18

0

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

answered Jul 11 '18 at 10:18

sridhar

321
1
3
18

you should post an answer only if the technique or approach you are using for the answer isn't mentioned in any of the other answers, which isn't the case here. – Muhammad Omer Aslam Mar 27 '19 at 10:31

Yii2 Active Record using FIND_IN_SET in where clause

2 Answers2