Search comma separated value using Yii2 Active Record

Yii 2.0 General Discussions
1

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: "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. Th 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

I would try like this:




    $modelfedbkshiprate = \backend\models\Product::find()

        ->where(['product_category' => $data["key1"]])

        ->andWhere('find_in_set(:key2, `product_subcategory`)', [':key2' => $data["key2"])

        ->andWhere(['company' => $data["key3"]])

        ->one();
3

Its showing a syntax error - syntax error, unexpected ‘)’, expecting ‘]’

on this line

->andWhere(‘find_in_set(:key2, product_subcategory)’, [’:key2’ => $data[“key2”])

4

Ah, sorry.




->andWhere('find_in_set(:key2, `product_subcategory`)', [':key2' => $data["key2"]])