Search comma separated value using 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


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




function p1() {

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


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






                    error: function (exception) {








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.

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"]])


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

on this line

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

Ah, sorry.

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